shawnb18

I setup a word name "clock" to =TEXT(NOW(),"h:mm AM/PM") so I can have a timestamp entered when someone types "clock", however, the problem is if someone typed it right now it would read 6:34 in cell A1 then tomorrow they typed it in the same sheet in cell B1 it would display tomorrows time and change A1 automatically to that time stamp. How can I make it to what time gets stamped on stays on. This is for an electronic time card that I am working on.

Thank you!

Glaswegian

Hi again

You are using the NOW() function which is volatile - in other words it updates each time the spreadsheet is opened or re-calculated. You need to use Date or Time. I'm pretty hopeless at formulae so I'm not sure the best way to do that. Could do it easily in code of course...

shawnb18

I think I am going to do it in code, I have it working in cells adjacent to a cell with a value in it which may work out best.

For example; to clock in you would put a X in cell C6 and then in cell D6 it would display the time.

Here is the code I am using...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C6:C12,E6:E12,G6:G12,I6:I12"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

What is the best way to also add the below code to this, the code you helped me with before,

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F9:F15,F21:F27,G9:G15,G21:G27,H9:H15,H21:H27,I9:I15,I21:I27")) Is Nothing Then Exit Sub
Sheets("Sheet1").Unprotect
If Target.Value <> "" Then
Target.Locked = True
End If
Sheets("Sheet1").Protect
End Sub

Thank you!

shawnb18

Wow I am making this very complicated but I have one more code I need help with and need to know how to incorporate it within that sheet.

Basically I have dates in cells A6:A12 and what i am trying to do is to have certain cells unlock if the date in one of those cells = TODAY

for example,

If A6 = TODAY then Unprotect C6,E6,G6,I6
ELSE PROTECT C6,E6,G6,I6

But i need this same idea to search and find todays date betwee A6:A12. The reason is so someone can only update their time card on the correct day. So if today is Friday 2.29.08, I can only put my X (to timestamp entry) in todays row. If I were to click on tomorrows date it wouldn't let me enter anything until tomorrow.

Thank you!

Glaswegian

I'll try and look at this over the weekend and get back to you. I think the wife has some kind of shopping trip planned...

shawnb18

I appreciate all your help. Good luck with the shopping trip, my wife seems to drag me along on one of those every weekend...

Glaswegian

Hi again

See if this works for you. A couple of things to point out. I noticed you had used NOW in your first code - as I said earlier, this a Volatile function that will update, so I changed that to the Time function, which simply uses the system time. Since you can only have one Change Event on any sheet, I created a separate sub for A6:A12 - it is called from the main change event. Note that you will need to format the cells in A6:A12 to the second format type in the date section of the format box i.e *14 March 2001, otherwise the sub will not recognise the text. Since there were only 6 cells involved, it's probably easier to loop through those six and test each value.
Post back with any problems - just in case I've headed down the wrong track.

shawnb18

Thank you! I am having a problem with this code though. It gives me an error when I try to enter the first X in C6 (I have also tried to enter something in the other fields and it comes up with the same error and line under debug) and when i goto debug the line, "If Intersect(Target, Range("A6:A12")) Then" is highlighted yellow. I have the dates in A6:A12 formated the way you requested by the way... Any idea where the hang up could be? Thank you!

Glaswegian

Hmmm....I think I know what I've done (or not done) - I'll get back to you - probably tomorrow.

shawnb18

Awesome, thanks!

Glaswegian

Been thinking about this - see if this suggestion has any possibilities.

You want some kind of electronic time card. At the moment, it looks like you are asking users to type the word 'clock' in a particular cell, and a time is then displayed using code. There is also an update process (some of which is causing some of the problems...).

How about using an input box? Perhaps a couple of buttons on a front sheet and the user has no access to any other sheets. Click one button to sign in - I'm guessing they will need to type their name - the time can be placed on any sheet and no need to use all these Change Events etc. You could do the same for signing out and have Excel calculate time worked or whatever. Hopefully you can see what I'm aiming at here - what do you think?

shawnb18

That does sound like a good idea. I would input a validation list of names and they just bring down the box, click there name and click the clock-in or clock out button which will then timestamp a seperate sheet and calculate the time.

The way i have it now is that they just put a X in cell under clock in and it time stamps the cell adjacent to it with the time, they click another cell to clock out for lunch and then one to clock in for lunch and a last cell to clock out for the day. Then using some basic calculations it adds the time up and decides if there is any overtime and inputs the data into a seperate payroll transmittal sheet used to upload into payroll system.

I like the idea of being able to click either clock in or clock out and transfering the data to a hidden sheet. What is the best way to do this? By the way if you would like I could send you a copy of what I have been working on if you would like to get an idea of what I am talking about. Thank you again.

Glaswegian

Sure, attach a copy (remember to remove any data you consider sensitive), although I have my own 'style', if that's the correct word, for doing things like this. I do try to keep it simple if possible.

The advantage of using an input box is that the users will only have access to one sheet - the rest can be hidden, and any menus that they might try to 'tweak' can also be hidden, or disabled.

To attach a file to a new post, simply

1. Click the[Manage Attachments] button under Additional Options > Attach Files on the post composition page, and
2. copy and paste the file into the "Upload File from your Computer" box:
3. Click Upload.

You'll need to zip or compress the file. I'll have a look and see what I can come up with.

shawnb18

There are 2 files in this zip. One is the original time card I was working on which has some basic calculations. The other is a version I was designing using your recomendation, however it has no code in it for the punch of the buttons. Thank you again!

Attached Files

NEW JRAS TIMECARD WITH TIMESTAMP revision 3 MASTER COPY with Examples.zip (85.9 KB, 14 views)

shawnb18

I have been working on this and I think the best way to do it is to have 4 buttons on the first page, clock in, out for lunch, in for lunch and clock out for day. The button when clicked should check the name listed in cell A3 and match it with a name in on the clock data sheet and then match the date from the main page with a date on clock data sheet and then unprotect the sheet to input the time stamp on the correct field and then protect the sheet. I recorded some macros but I can not get the lookup to work, to where it matches the name and date selected on sheet 1 (time clock) with the info on sheet 2 (clock data)...

Will you take a look and see if there is any help you can give me on this? Thank you for your help thus far by the way!

Attached Files

TimeCard with Button alpha copy 3.6.08.zip (59.9 KB, 18 views)

Glaswegian

Hi

Sorry - things have been a bit busy, but I have done some work on this (before looking at your versions). I should be able to devote time to this over the weekend.

Glaswegian

Tried to open your files but couldn't - I'm using XL2002 - I suspect you may be using XL2007?

How many staff will this be used by? My first take requires only one button, and uses a dropdown with staff names and 2 option buttons - one for sign in and the other for sign out. I'll need to add in some columns for lunch breaks, but it works OK at the moment.

shawnb18

Yeah i am using 2007. Sorry about that. Well we have about 230 people who would be using it but it would be seperated by location and department which would break down to about 20 people per department. What you just said is almost exactly what my spreadsheet looked like, just without functioning buttons. I had them set to a macro which would goto the right sheet and enter the time but only for one user. I am also thinking of going another way with this... Once again we are just using an old manual time clock to punch in and out and this takes payroll FOREVER to punch each entry into our payroll system... I am thinking of implimenting a company intranet and using a online timeclock using asp... One of our sister companies is doing this and it seems to be working well for them, the only problem is the maintence it requires.

Glaswegian

Hi

Do you want me to keep working on this just now, bearing in mind your comments about an online database?

shawnb18

No, lets hold off. i am going to be doing some more reasearch into the online version so I am going to just put this on hold for now. I will get back with you possibley in the future. Thank you for all the help and time you have put into this thus far. I am sure I will be back here asking you more questions later... Take care!