# 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)
###### RE: Build a simple timesheet in Excel
jamesxxx 20th Mar 2010
Hi, I am creating a timesheet that has needs both daily overtime and overtime after 40 hours worked. So in your timesheet ,for example, works 10 hrs on Monday then that is 8 straight and 2 ot, then he works until Saturday at 8 hours per day, so Saturday should show no straight hours worked, only 8 ot hours. How can I have a formula that calculates daily ot and ot when over 40 hours. I need both as this is common
###### Need Daily OT AND Weekly OT
rdombroski Updated - 22nd Apr 2010
In our state employees are paid OT for over 8 hours in a day in addition to 40 hours in a week. The simple timesheet would work for me if it factored this in. Anyone have any ideas? I am driving myself crazy trying to figure this one out!
###### RE: Build a simple timesheet in Excel
swifttime 12th Apr 2010
The Swifttime is an electronic web based application making it easy for employees and managers to improve work efficiency. Best suited for firms with remote employees and companies with hourly employees where proper documentation of working hours are required. This application can be accessed from any web based device like mobile devices(Blackberry or iphone). It has following advantages:

? Provides a menu driven user interface.
? High accuracy in payroll management.
? Employees and managers can easily access this application via Internet or mobile devices.
? No start-up cost is required.
? Nightly backups of all data.
? Helps to ensure compliance with federal wage and hour laws.
? Integrated billing and invoicing module.
? Data can be exported to Excel spreadsheets for further processing.
? Integration with payroll programs like ADP, Quick Books, Peach tree and others available

Visit : http://www.swifttime.me/
###### 1.5x and 2x overtime
joelec 16th Aug 2010
Our company goes from overtime to double time after 3 hours of overtime. How do I adjust the overtime formula and recreate the double time formula.
###### error in overtime result
evienne 4th Apr 2011
how should i correct it?
###### Calculate Hrs Worked Based On 24 Hour Clock
xcel-pro 12th Jun 2011
All,

Someone on Mr Excel helped me with the formula for working past midnight some time ago
Here is the formula: =ROUND((MOD(G2-D2,1)-MOD(F2-E2,1))*24,1)
Tested for several different scenarios, worked for every test case. You will need to enter all times in Military time (24:00) Ex: 12 midnight = 00:00, 8am = 08:00, 2pm = 14:00 (12+2)
###### =A8 doesn't work
Brian8899 2nd Dec 2011
I think you meant =A7. When i put =A8 into the specified cell the days started on the wrong day the last day didn't come out since it was moving into a blank cell
###### weekly timesheet to annual timesheet
MiriahK 5th Dec 2011
is there an excel tutorial on how to formulate all my weekly timesheets to be input to a yearly timesheet.
###### Need formula translation
maryannwaring 28th Dec 2011
The timesheet I'm working on is for a salaried employee and has hourly breakdown columns of "straight", "holiday" and "maternity leave"...no "overtime" column. If a person works 9 hours in a day, I don't want the extra 1 hour to go to "overtime"...I want it to stay in the "straight" column. But, without translation of the current formulas, I don't know what adjustments to make to the formulas to make them come out right. Can someone help?
###### Runnign reports
jen2swt 4th Jan 2012
What is the best way to run a report from said Timesheets? I am using this recordkeeping. Say I want to run a report for said employee to see the first day they started and the last day they worked.

What is the best way to record. Should I do a timesheet for all weeks or just a continous workbook for each employee?
###### Timesheet example
excelinexcel Updated - 12th Feb
Great tutorial but a bit long. If you are looking for a quick and easy timesheet calculator you might like: http://www.excel-easy.com/excel-examples/time-sheet.html
###### time sheet example
tiemle 12th Sep
Great example. Thanks so much
###### Time Sheet
raj_as Updated - 29th Nov
Good explanation in manage our time in excel sheet but important to every one
Now days, all humans know about how much time to spend in one project? and how to schedule it to improve. Time Management is very essential to utilize your work and your employ's work..
Now, we started to develop [Time sheet] : http://qualitypointtech.net/timesheetdemo/index.php web application
It is best and efficiency application for monitoring working time and available for employee wise/Project wise/Date wise time report. Just work with our [Time Sheet DEMO VERSION] : http://qualitypointtech.net/timesheetdemo/index.php
More Details about [TIMESHEET Application] : http://qualitypointtech.net/webtimesheet/index.php
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.