# How do I make a timesheet?

June 16, 2009 at 16:02:32
Specs: Macintosh
 I have Microsoft Office, and we've usually used the honor system for employees to clock in, however, we've had a few recent problems.I know there is a way to input a formula so that as soon as something is entered in a given cell, there is a time stamp, however, I can't seem to find this formula?! Anyone know? I'm fairly competent with computers, however, I'm simply stumped on this one.Any help is much appreciated!

See More: How do I make a timesheet?

#1
June 16, 2009 at 18:02:04
 there is a time stampNot sure what you mean by this.Do you mean enter a date/time into a cell?There a lots of timesheet templates out on the web and a great many are free.You could also try right on your machine:Start ExcelOn the Task Bar:FileNewWhen the new workbook task pane opens look around for a Timesheet template. Possibly under Spreadsheet Solutions.MIKEhttp://www.skeptic.com/

Report •

#2
June 16, 2009 at 18:14:03
 You mentioned a "formula"...Well, this formula will put a time stamp in a cell as soon as something is entered in A1, but that time stamp is going to update whenever any change is made to the worksheet while there is something in A1.=IF(A1<>"", NOW(),"")On the other hand, this macro will put a time stamp in B(row) whenever a change is made to A(row) and that time will be a number that won't change - unless that same A(row) cell is changed or your "problem" employees change it. There are ways to protect those cells...let me know if you are interested.```Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Target.Offset(0, 1) = Time End Sub```

Report •

#3
June 16, 2009 at 21:06:56
 Basically, what I'm trying to do, is set up a template that I can use every week. The idea is that the employee will come in, open the template in their personal file and "clock in".I would like to do this in a weekly layout. So, for example, "A2" will read "Monday", with "B1" labeled "In" and C1 as "Out". Under B1 and C1, the "time-stamp" will appear when the employee clocks in, giving an exact time.Does this make more sense now? I'm sorry for any confusion, and am sincerely thankful for any help!

Report •

Related Solutions

#4
June 17, 2009 at 07:40:04
 If you wish too make your own, we will be glad to lend assistance. the "time-stamp" will appear whenthe employee clocks in,Is the employee going to input this?If you would prefer a prepackaged deal with the work already done, see if this is what your looking for:http://office.microsoft.com/en-us/t...MIKE

Report •

#5
June 17, 2009 at 08:17:20
 Yes, the employee will enter it on their own, but I want to make sure it is an exact time. Basically, I'm looking for a very easy solution where one can come in, open the folder and either click a cell, or enter a time, but if they enter that time, that it be an exact and correct time.I will check out that link, thank you!

Report •

#6
June 17, 2009 at 08:24:19
 I downloaded the template, but could the employee just enter a different time? For example, if they arrived to work at 8:05am, what is there to stop them from inputing 8:00am?Sorry if any of this is inconvenient.

Report •

#7
June 17, 2009 at 08:29:15
 if they arrived to work at 8:05am, what is there to stop them from inputing 8:00am?In the template you downloaded, nothing.It would need to be modified, probably with a macro similar to the one DerbyDad03 suggested.MIKEhttp://www.skeptic.com/

Report •

#8
June 17, 2009 at 08:33:27
 Is this something someone with a basic understanding of Excel can do?

Report •

#9
June 17, 2009 at 15:00:04
 It should be, but unfortunately my macro skills are just above nil.Hopefully, DerbyDad03 should be able to point you (and me) in the right direction.MIKEhttp://www.skeptic.com/

Report •

#10
June 17, 2009 at 18:25:39
 @Mikere: "unfortunately my macro skills are just above nil."And what steps are you taking to raise your skill level?@ Riquebuttre: "Is this something someone with a basic understanding of Excel can do?"Perhaps the question should be:Is this something someone with a basic understanding of Excel and VBA can do?Yes.1 - Open the blank template and use the control key to select the 28 empty Time In/Time Out cells.2 - Once these cells are selected, click in the Name Box above the row numbers/Column A.3 - Type in UserTimes and hit Enter. This will name the range that contains those 28 cells. We'll be referring to that Named Range in the code below.4 - Right click the sheet tab and choose View Code.5 - Paste this code in the window that opens.```Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if selected cell is within the UserTimes named range If Not Intersect(Target, Range("UserTimes")) Is Nothing Then 'If yes, check if selected cell already has a time in it. If Target <> "" Then 'If it's not empty, pop up a fake password request MsgBox "Please Enter Password To Make Changes" 'Select the date cell for this day Cells(10, Target.Column).Select Exit Sub End If 'If selected cell is empty, put in the time Target = Time 'Select the date cell for this day Cells(10, Target.Column).Select End If End Sub```6 - Go back to the template and click on any of the Time In/Time Out cells.7 - Try to change that time.There are shortcomings to this solution, but that's enough for now. If this method intrigues you, come on back and we'll discuss the additional things that need to be dealt with. In fact, I'd like you to come back with a list of the shortcomings that you find.

Report •

#11
June 18, 2009 at 08:10:53
 First let me say that I don't think modifying any spreadsheet will solve his problem. His first statement:we've usually used the honor system for employees to clock in, however, we've had a few recent problems.Signals to me a personnel problem. Solving that with a “technology” fix probably won’t work.As for the VBA code:Since Excel time is reliant on the OS time, you simply change the time on your computer and Excel records it. ( I know this can be locked down but that’s an OS question.)Also you can change the time using Search & Replace.As hinted at with the “Enter Password”, some type of mechanism will need to be created so the Admin can modify if needed.MIKEhttp://www.skeptic.com/

Report •

#12
June 18, 2009 at 08:35:46
 Your post indicates you have multiple users.Is this a server based environment? If it is you can have the server audit the user login / out times and make comparisons with the honour system.http://technet.microsoft.com/en-us/...There are also web based timesheet applications available... these could cause employee disgruntlement... but when priveleges are abused.....EDIT: Didn't notice the OS.Might be food for thought though.

Report •

#13
June 18, 2009 at 09:19:22
 As I said in my earlier post "There are shortcomings to this solution".These shortcomings can be addressed in various ways, but obviously things get more complicated as more and more safeguards are added. I started slow just to see if there was any interest in a macro based solution. As you mentioned, the OS time can be locked down. That addresses the OS time issue. I haven't looked into it, but there may also be a way to pull the time from an external source, like a server or webpage - someplace that the user can't access.Worksheet protection can prevent changes via Search and Replace. In this case the code becomes more complicated, but some sort of protection is required anyway, otherwise the users could edit/delete the formulae and blow up the whole worksheet.Finally, the code can be hidden and password protected so that users can't access it. For an admin to make changes, all it takes it to unhide the code, add a single strategically placed apostrophe, and the admin can make all the changes they want to both the code and the worksheet.Compare it to securing your house. With each additional piece of security - from an extra deadbolt that requires another key, to an alarm system that requires a code, to a couple of junkyard dogs that need care and feeding - life for the owner gets a little more complicated.Very few things come without some price to be paid.

Report •

#14
June 18, 2009 at 19:57:47
 Just 'cuz I never did it before...(I like to learn)...I came up with a way to get around the system time clock issue.I added a sheet to the template and created an external query to http://www.timeanddate.com/worldclo...which imports the current time (EDT) from the World Clock sitehttp://timeanddate.com/worldclock/I set the query to update every minute.Then instead of Target = Time, I use Target = Sheets(2).Range("A3")which is where the time ends up via the query.Of course, the TimeSheet still needs to be protected, Sheet2 needs to be hidden, the code needs to be protected, etc., etc., etc., but at least we've eliminated the user's ability to falsify the time without having to lock the system time clock.

Report •

#15
June 18, 2009 at 20:23:26
 Quite ingenious....Edit:What happens if internet connection fails?Fall back to OS time?MIKE

Report •

#16
June 19, 2009 at 04:32:06
 Could...or the concept could be used to grab the time from a server internal to the business.Hey, nothing's perfect. If everything was perfect, this thread wouldn't even exist, right?

Report •

#17
June 19, 2009 at 10:39:38
 I'm not worried about someone tampering with the time, as it is simply not an issue.

Report •

#18
June 19, 2009 at 11:05:40
 If the time isn't an issue, are you worried about them tampering with the code or with the formulae in the cells?Keep in mind that "tampering" implies intent, while "curiosity" can be just as harmful to a spreadsheet.At a minumim, I would unlock the 28 cells where time is to be entered and protect the sheet. At least that way users won't be able to mess with the formulae.One last thing...The code selects the date cell after the time is entered. The reason for this is to force the user to reselect a time cell if they want to manually change the time, which in turn causes the fake password request to pop up. Therefore, you have to either make sure that the protection allows the users to select locked cells or pick some other cell that is unlocked so that the code doesn't puke when it tries to select the date cell.P.S. How do you know that your users aren't following this thread and now know how the whole thing works?

Report •