# Discussion on: Build a simple timesheet in Excel

38

### Join the conversation!

View:
Show:
###### LOOKS SO GOOD
malcch@... 27th Jan 2010
OK I will check this out. You have done a lot of work on this. Well done
###### RE: Build a simple timesheet in Excel
hdhokia 27th Jan 2010
Simple???!!!
###### How about a bit more automation?
mark@... 27th Jan 2010
Use a list to define quarter-hourly (or other) timeslots. Allow for more than a lunch in the day. Allocate and sum project codes.

www.helpdesk.net.au/downloads/Afras.xlsx if an offering for any that want to build on it
###### RE: Build a simple timesheet in Excel
jblvlma37@... 27th Jan 2010
A Very good breakdown of a how to. I applaud your site and it help to the people like myself whoare self taught in both software and hardware. Thanks a whole lot. I will be using this for myself and my sister in laws business.
###### Formula error in article.
stephenc@... 27th Jan 2010
Better check the formulas for calculating the hours for each day.

The same formula =IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24) is used for both the first 8 hours and for the overtime.

The overtime calculation should be:
=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)
dwight.wright@... 27th Jan 2010
I noticed the same issue but validated tht the downloaded file has teh correct formula
Pro
###### Formula fixed
JodyGilbert 27th Jan 2010
Nice catch -- thanks very much!
--j
Contributr
###### Nice catch
ssharkins@... 28th Jan 2010
Thank you so much -- I apologize for the inconvenience.
###### How to Include Compensatory Time Calculation
michael.barnett@... 27th Jan 2010
This is great and doesn't seem so "simple" to me However, I also have to compute (during the same work week) any compensatory time our non-exempt employees earn each week. Whereas overtime is total hours over 40 that the employee actually works, our employees accrue compensatory time when any combination of work, vacation, sick, etc hours total more than 40 during the week. Any "simple" solution for this would certainly be appreciated.
###### CTO
broccili 27th Jan 2010
Mike,
Do they accrue time off at a regular pay rate, or at a time-and-half (overtime) rate?
The simple solution up front: if you built the spreadsheet from this tech republic article you have an Overtime field, and that would be your hours OT for a pay period. Might need to tweak the scale/calculations to adjust for your employees!
###### CTO
michael.barnett@... 27th Jan 2010
They accrue at regular rate and the spreadsheet would have to be configured, if possible, to differentiate between overtime or comp time while providing a weekly, or in our case, monthly total. Way over my head for sure!
Contributr
###### Tell us exactly what you need
ssharkins@... 28th Jan 2010
We can come up with a formula for you -- but I need all the details. Now, the overtime in this particular sheet is greater than 8 hours worked in any given day, but it only considers actual hours worked. You want anything over 8 hours in a day that also considers sick time and vacation? That one confuses me a bit -- if an employee took 4 hours of sick time and then worked 5 hours, they'd be an overage of 1 hour. Why wouldn't the employee just claim 3 sick hours instead?
###### Overtime vs Comp Time
michael.barnett@... 29th Jan 2010
Thanks for your response. The key for us is not how many hours an employee works per day, but per week. (Our work week is Sun-Sat.) We are not tied to working an 8 hr day. For exampe, my supervisor grants me the latitude of a flex schedule and I work 8.5 hrs M-T, then 6 hrs on Fri. Even though I work more than 8 hrs per day, my schedule only totals 40 hpw. If I happened to work 7 hrs on Friday, then I would accumulate 1 hr of comp time for the week. It's the same for n/e personnel, except they do get the overtime for hours physically worked over 40 per week. Hope this makes sense.
Contributr
###### Overtime
ssharkins@... 30th Jan 2010
The example sheet still handles your situation, doesn't it? You just go buy the total at the bottom rather than the daily subtotals. Wouldn't this work?
###### Overtime vs Comp Time
michael.barnett@... 29th Jan 2010
Another thought I had, just to give you more detail. It is possible for a n/e employee to accrue overtime and comp time in the same week. Example: Monday is a holiday (8 hours) then the employee works 11 hpd Tue-Fri. Total hours for the week would be 52 hours. Of that, 4 would be computed as overtime (x1.5) and 8 would be computed as comp time.
Contributr
###### Holidays
ssharkins@... 30th Jan 2010
There's no column for holiday pay in the sheet. You could easily add one. Or, you could use what's there, as is and enter holiday hours as vacation. However, I think if an employee gets paid holiday pay, a separate column would be easier to track.

The sheet doesn't deal with compensation, but that would be easy enough to add by adding a row that included rate for each column and totaling them.

I don't think most companies would honor comp/overtime in a holiday week, but you know, they might. That's why the sheet is so simple -- every company has their own policies, but this sheet can be easily enhanced to provide more information.

Excel can do it all for you, if you use the right formulas!
###### RE: Build a simple timesheet in Excel
Aser10 27th Jan 2010
very nice
###### simple timesheet
dhays 27th Jan 2010
Ours was a little more complicated as it allowed 3 sheets at a time active, when the fourth two week period was opened the first of the three became no longer active, any changes made in it no longer were automaticaly calculated nor added to the annual summary sheet. It was setup for a 26 payperiod year, if we happened to have a 27 payperiod year (once in a great while), then it took more work. The gentleman who had set it up had formulas for everything, in locked hidden cells, that not only provided for calculations but for drop down lists, automatically popluated the days of the week, the payperiod span, the number of hours for overtime, comp time, anyother time not worked...We have since been moved to an Oracle based system that does about the same thing, we also had spreadsheets for our Labor Distribution Codes to help management figure out how their money was being allocated, and for the technicians, it was a way of "charging" their time to a project, the new system is similar, it just standardizes timekeeping across the different divisions, providing a central database accessible from any computer.
###### RE: Build a simple timesheet in Excel
RRB 27th Jan 2010
The tricky part is entering the time values. In figure H, they are entered as decimal numbers, meaning that 8.5 is actually 8:30. It is easier, up to a point, to enter 8:30, 17:16 or whatever time you like, directly and I concur with the author in using 24 h format as it is easier.
Excel uses a convert the calendar and hour systems to a decimal system. 1 is one day, 0.5, 12 hours and so on. That way it facilitates the calculation.
Try entering several hours as 8:15, 8:20 and so on, and then format the number as general and you will see the decimals.
If an employee works past midnight, you will have to enter the date also. For example: In 1-10-10 22:00, Out 1-11-10 6:00. That way you are always substracting the lesser from the greater number, and you don't get negative numbers.

If you want a speedy way to enter time values, you might consider using one column for the hour, another for the minutes and another to convert. For example; A2=8, B2=30, C2 contains the following formula =A2/24+B2/24/60 and then format as hours.
###### Working past midnight
bobjorg@... Updated - 28th Jan 2010
That is a good point about employees whose shift or overtime period includes midnight. Such "graveyard shift" workers must enter the date as well as the time. Is there a way to simplify this?
Most Excel useers don't realize that it comes with the programming language VBA (Visual Basic for Applications) which can automate much of this. However, VBA can be complex to learn since many of the features are not intuative.
Contributr
###### When time expands into the next day
ssharkins@... 31st Jan 2010
I think there's a formula that can handle expanding midnight, without requiring both dates -- I'll see if I can find it.
###### RE: Build a simple timesheet in Excel
happymedia_dz@... 28th Jan 2010
Verry interesting
Happymedia_dz
###### Overtime in Saturday and Sunday
This timesheet is simple, but I need to add something in formula for not calculating Saturday and Sunday as regular hours.
We use monthly timesheets, sometimes we have to work on Saturday and Sunday (usually Saturdays and Sundays are overtime hours, in rare cases are compensation hours)
My question is how to make the Saturday?s and Sunday?s hours as overtime?
Contributr
###### The sheet doesn't calculate compensation
ssharkins@... 2nd Feb 2010
You probably don't need a complex formula. First, your overtime hours for Sat and Sun are already there in your subtotals -- any Sat and Sun time is overtime, right? Just sum them. Then, subtracting your Sat and Sun hours from the straight time would compensate there, right?

If I misunderstood your requirements, just let me know.
###### The sheet doesn't calculate compensation
?any Sat and Sun time is overtime, right? Just sum them. Then, subtracting your Sat and Sun hours from the straight time?
For the time being I am doing the same, but this has to be done ?manually?, and when I do that it happens that time to time I forget some hours for our employees.
So I believe that having a complex formula that makes hours of Sat and Sun as overtime would saved me (and probably many other, too). I believe that formula has to be something like this
on G7 =if(B7=sat or sun,0,(then the other part of the formula), on H7 =if(G7=0, sum (c7:f7), (than the other part of formula), (I have tried this but I got lost)
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.