I often get queries on how to calculate regular hours and overtime hours of employees based on the ‘In time’ and ‘Out time’. I also couldn’t find a good Excel timesheet template online, so I decided to make one. Click here to download the Excel Timesheet Calculator This is a dynamic timesheet template in Excel where you can change the start date and the weekends. You can also specify hourly rates (regular/overtime). There is a weekly, bi-weekly, and monthly timesheet template in the download file (each in a separate worksheet tab). Also, when you print this timesheet template, it will fit perfectly on a single page.
Excel Timesheet Calculator Template
Here is a snapshot of the Weekly Excel Timesheet Template:
As soon as you enter the ‘In time’ and the ‘Out time’, the template automatically calculates the regular and overtime hours. If there are any breaks (such as lunch breaks) that are not paid, you can also enter that. Based on it, it also calculates the total pay (considering there are hourly rates). This Excel template is available in three formats – Weekly Timesheet, Bi-weekly Timesheet, and Monthly Timesheet (provided as different tabs in the download file).
How to Use this Excel Timesheet Calculator Template
Here are the steps to use this Excel Timesheet Template:
Select the Week Start Date.
Specify the weekend. You can select from various options in the drop-down. The options include – No Weekend, 1-day weekend (Mon, Tue…) or 2-days Weekend (Fri & Sat, Sat & Sun..). As soon as you select the weekend, those days get shaded in red on the timesheet.
Specify the Start Time, # of Regular Hours, and Hourly Rate (Regular and Overtime). The start time should be in the hh:mm format (24-hour format). For example, 6 AM would be 06:00 and 6 PM would be 18:00.
If all the Weekend hours are to be treated as overtime, select the checkbox. If unchecked, weekend hours would also be split into regular and overtime hours.
Enter the In and Out time for a date, and break hours (if any). This Excel timesheet has formulas that will automatically calculate the total number of Regular hours and Overtime (OT) hours. Note that break hours are deducted automatically from regular hours.
A couple of points to keep in mind while using this Excel Timesheet template:
There is an inbuilt check to make sure ‘In time’ is not later than the ‘Out time’. The template would not let the user enter the time in such a case. [This has been made possible using the data validation rules]. If the work shift of an employee or team member spans to the next day (for example, starts at 6 PM and ends at 6 AM the other day), then make sure Day 1 time is 18:00 to 24:00 and Day 2 time is 0:00 to 6:00. Do not change any formulas in the timesheet. Only make the entries in the ‘In time’ and ‘Out time’ columns. While deleting entries, delete it only from the ‘In time’, ‘Out Time’, and ‘Break Hours’ columns. I have changed the page margins to make it fit on a single sheet when printed.
What went into making this Excel Timesheet Calculator template
Excel Formulas: A number of Excel functions such as DATE, MATCH, INT, IF, and IFERROR is used to calculate the values (such as the date from selection or regular/overtime hours) in this timesheet template. Excel Drop Down List: It is used to allow the user to select the month name. Check Box -it is used to allow the user to specify if the weekends are to be charged at the overtime rate or not. Named Ranges/: These are used to refer to the data in the back end (in the data tab). Conditional Formatting.: It is used to highlight the rows when a given date is a weekend.
Download the Excel Timesheet Calculator Template
If there is an Excel template you wish existed, let me know in the comments section.
You May Also find the following Excel Templates useful:
Calendar Integrated with a To Do List Calculate Time in Excel (Time Difference, Hours Worked, Add/ Subtract) Excel To Do List (4 Templates). Holiday Calendar Template (US Only). Project Management – Employee Leave Tracker. Shared Expense Calculator. Vacation Itinerary and Packing List Template. Excel Calendar Template (Monthly & Yearly)
If you are looking for an online timesheet calculator, check this. Thanks Let’s say your employee works 35 hours one week, 40 hours the next, 37 hours the third week, and 45 hours the following week. You pay them a fixed rate of $800 per week. If you want to determine the employee’s hourly rate from week to week, divide their weekly salary by hours worked: Week 1: $22.86 per hour ($800 / 35) Week 2: $20 per hour ($800 / 40) Week 3: $21.62 per hour ($800 / 37) Week 4: $17.78 per hour ($800 / 45) You must calculate the employee’s overtime pay for the week they worked 45 hours. To find the employee’s overtime rate, multiply their week 4 hourly rate of $17.78 by 0.5, or divide by two: $17.78 X 0.5 = $8.89 Now, multiply the employee’s overtime pay by how much overtime they worked (5 hours): $8.89 X 5 = $44.45 Finally, add the employee’s overtime pay and their fixed salary to get their total pay for the week: $800 + $44.45 = $844.45 With overtime, you must pay the employee $844.45 for the week. Looking for a monthly timesheet starting 20th of every month to the 19th. Monday to Thursday, but paid in 15 minute intervals, so if I clock in at 0520, I get paid from 0530, if I clock out at 1720, I get paid to 1715. as the round it back if before clock out early, and round it forward if I clock in early. So a time sheet, with Day, Date, Start Time, 30 Min Break deduction, Finish Time, Showing my 10 hrs normal time with the break, anything over is overtime, so 10hrs 30mins normal, as they deduct the break, At the bottom, total hours worked, plus total overtime at the bottom? I can’t get it myself, tried, tried and tried, without any success. Can you get 24hr clock displayed, so start time 0520 and 1730 end time ? Any chance you can throw one together, so I can tweak it if needed? Appreciate if you can guide me on how to set checkbox for Public Holiday Thank you, It’s a nice excel file template for a normal office job. But, in some situations, persons work more hours non stop for emergency situations (IT).. in this case, this file is not showing how to…. example starting 08:00 am on 1st of a month and ends at 03:00 hrs next day morning non stop. The excel sheet can’t go beyond 23:59hrs..!!! Hope to consider it Also I have very specific things I’m tracking like tips. Tip outs, cash tips credit tips etc… if I download your template can I add columns for these areas?? As mentioned in some of the comments below has anyone managed to find a way to remove the start time section so that the clock in time is the start time for each day? Thanks Aaron It’s a nice excel file template for a normal office job. But, in some situations, persons work more hours non stop for emergency situations (IT).. in this case, this file is not showing how to…. example starting 08:00 am on 1st of a month and ends at 03:00 hrs next day morning non stop. The excel sheet can’t go beyond 23:59hrs..!!! Hope to consider it Cheers..! 🙂 Thanks for this great timesheet. 🙂 This time sheet is quite interesting, even if I had to tweak it to fit my needs. However, and as several people have pointed out in different ways, this time sheet is not good for the U.S., because for the most part, states regulate that OT starts on the 41st worked hour in a week. Those states in which OT starts after 8 hours worked in one day, working from 8 to 5 yields 8 hrs reg pay + 0 OT hrs. Usually, in the states where OT starts after 40 hrs, trusting that one will work those 8 hrs a day for the full five days, for time-keeping purposes 8+OT is used on a daily basis for ease of calculating the week’s pay. Now, the 7th day worked is payed entirely at 1.5x the hourly rate, but OT on that 7th day is payed at 2x… again, some states do, some don’t. Generally speaking, the unpaid break hours are taken from the OT hrs instead of the 8-hr base for the day. Some states also allot for working at night and/or on holidays, to make things a bit more complex. In order to automate all this in Excel, it is probably best to set the base hourly rate in one cell, and use the percentage increment on the hourly rate in separate cells, instead of having to set the different hourly rates by hand. So, let’s say that in column K we have K2= hourly rate = 12 K3 = Reg OT = 1.5 * K2 K4 = DT OT = 2 * K2 All the OT and DT (double time) checking and calculations should take place in the existing OT column. Cheers. P.S. – Btw, the three time sheets read Weekly up top. Sat – 5:00 = x2.0 5:00 – 12:00 = x1.5 12:00 sat – mon 5:00 = x2.0 The Reg pay formula must calculate that if OUT – IN <= 9, the unpaid break gets deducted, otherwise Reg hrs will 8, and the break gets deducted from the OT hours. If OUT – IN 9, in this example, or 8.5 hrs if only a half-hour break is given. OT = OUT – IN – 8 – Break Because Excel reads time as fragments of a 24-hour period, so, for example 12 hours = 12/24 = 0.5 then 8 and the break time must be expressed as 8/24 and x/24, respectively, where x is the time lapse of the break in hours and/or parts of an hour in decimal form… half an hour = 0.5, 45 mins = 0.75, 1 hr 15 min = 1.25, and so on. So, as an example with an hour and a half break, OT hrs = (19:00 – 7:30 – (8/24) – (1.5/24)) * 24 “* 24” to convert it to decimal, and in this case OT hrs = 2 That is the logic behind it, but now you would need to follow and understand those two formlulae used in the worksheet so you can modify them to do as above. I’m sorry that I am not posting a ready-made solution for you, but I not only modified the formulae quite a bit, but also part of the layout of the timesheet, so mine would not be suitable. HTH. Cheers You are a life saviour! Question, my staff start time changes everyday, if I set 9am, anytime go later than 9am would consider as OT. But it shouldn’t as their working hour is 9 hours with 1 break, more than 9 hours would then consider as OT. How should I resolve this? Truely, Rajatangshu Saha HTH… Cheers Your days are ahead of the US by one only until it becomes after midnight in the US. In other words, on, say, Fri June 30th, Australia will reach midnight and becomes Sat July 1st, some 15 hours before the US East coast does, so in NYC it is still Fri Jun 30th at 09:00 a.m. 15 hours later, while it is 3:00 p.m. in Australia, NYC, at the strike of midnight, is coming around to Sat July 1st also, and both would be on that date until Australia hits midnight again. It is impossible for a date to fall on two different days of the week on any two parts of the world at the same time. Cheers, mate. Currently, if you look at the weekend, whether or not you tick “weekends paid at OT rate”, the time is broken down in to Regular Hrs & OT Hours. Is there a way we can list all hours worked on the weekend as OT Hours? Not for the sake of calculating, the pay, but to figure out how much OT employees are working? HTH… Cheers! Please can somebody help me out with this template, I have 2 shift patterns with my job and those times start it 13:00 and out time is 01:30 & 03:30 and out time 14:00 Please can someone help me out with this Template: excel-timesheet-calculator My Email is pauljohn75@outlook.com Cheers Paul Please advise Thank you Hourly rate (hrate) = 25 Reg Hrs = 8 Break = 0.5 OT = (OUT – IN – (8/24) – (0.5/24)) * 24 “* 24” to make OT a decimal number if OT > 3 then OT1 = 3 and OT2 = OT – 3, so OT1 pay = OT1 * 37.5 and OT2 pay = OT2 * 50 which in this case would be, OT = 5.5 hrs, OT1 = 3 hrs, and OT2 = 2.5 hrs Total pay = (RH * hrate) + (OT1 * (hrate * 1.5)) + (OT2 * (hrate * 2) Total pay = (8 * 25) + (3 * 37.50) + (2.5 * 50) HTH… Cheers! I am looking to do something similar but for shiftworkers. I do not need an overtime rate but need to count anti-social hours which is anything worked between 19:00 Friday and 07:00 on Monday (i.e. over the weekend) and then between 19:00 to 07:00 for the rest of the week. I am also having problems with how to get the shift finish time to be the next morning, i.e. for late/night shifts which start one day and finish the next…. Thanks Can you add it to your timesheet for me to download Please. For ex, my start time is 8:30am and end time is 5:00pm with 30min no paid lunch. That would be 8.5hr/day in office so I get paid 8hrs of regular pay working hours. Spreadsheet works great calculating OT if I stayed late as long as I start on 8:30am sharp. But if you came in 30 mins late at 9:00am and stayed late til 5:30pm to make up for the missed hours then the timesheet doesn’t calculate the hours correctly. It would say regular hrs are 7.5 and OT hours are .5 but it would be really just 8hrs of regular pay because you came in 30 mins late. I think the fomula calculates any hours worked outside the normal shift window is always overtime but not in this case… Please help Urgently normal hours 07:00 – 24:00 Daily, outside these hours are double time. Daily OT breaks down as 8 x normal, 2 x 1.5 and anything over at double time After 38 normal hours worked in a week the next 2 are 1.5 and anything over is at double time. Any asssistance would be greatly appreciated Cheers Thank you so much, finally i found very good template. I’m shuba from Malaysia. i need some changes on the template, can help me on it. Start Time Regular Hours Regular Pay (hourly) Overtime Pay (hourly) Overtime(Sun) Overtime(PH) 7.00 8 9.50 14.25 19.00 28.50 This is my workers rate. If the day change to SUN it must automatically change to SUN OT Pay. =IFERROR(IF(AND(D16<>“”,E16<>“”),IF(D16>$C$12+TIME($D$12,($D$12-INT($D$12))*80,0),0,IF(E16>$C$12+TIME($D$12,($D$12-INT($D$12))*80,0),MIN(TIME($D$12,($D$12-INT($D$12))*80,0),($C$12+TIME($D$12,($D$12-INT($D$12))*80,0)-D16)),MIN(IF((E16-$C$12)<0,0,(E16-$C$12)),(E16-D16))))*24,"")-F16,"") It may Help you if i can make a proposition: this is perfect for an individual employee , but what if you have more employees and you want to have everything in one file ? it would have been nice to have one , but i imagine i can do a sheet of the weekly or mothly calculator for each employee and make a summary sheet using indirect formula. nevertheless super job
