# Excel Template of the Month (July 2015) – Employee Timesheet Calculator Template

‘Excel Template of the Month’ is a series where I share useful Excel Templates that can lead to higher productivity and/or automate some routine tasks. This is the second part of that series.

If you missed the first one, here it is – Calendar Integrated with a To Do List.

This month, I am sharing an Employee Timesheet Calculator Template in Excel.

Since I have started blogging about Excel tips, timesheet calculator template has been one of the most requested ones. I often get queries on how to calculate regular hours and overtime hours based on the ‘In time’ and ‘Out time’. I also couldn’t find a good Excel template online, so I decided to make one.

This is a dynamic template where you can change the start date and the weekends. You can also specify hourly rates (regular/overtime).

It fits perfectly in a single page when printed.

#### Employee Timesheet Calculator Template

Here is a snapshot of the Weekly Timesheet Calculator:

As soon as you enter the In and Out time, the template automatically calculates the regular and overtime hours. If there are any breaks (such as lunch break) that are not paid, you can also enter that. Based on it, it also calculates the total pay (considering there are hourly rates).

This template is available in three formats – Weekly, Bi-weekly, and Monthly.

##### How to Use Timesheet Calculator Template
• Select the Week Start Date.

• Specify the weekend. You can select from 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 in 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 check box. 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). It 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 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 an employee work shift 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 formula in any cell. Only make the entries in In time and Out time column. 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 a single sheet when printed.
##### What goes into making this Timesheet Calculator template:

If there is an Excel template you wish existed, let me know in the comments section.

If you use a template that you would like to share with our readers, let me know in the comments section or shoot me an email. I would love to hear from you.

###### Other Free Excel Templates:

If you are looking for an online timesheet calculator, check this.

• Mani

This is nice. I often do this manually but I can tweak this to suit my work. Thanks for sharing

• Thanks for commenting Mani.. Glad you liked it 🙂

• Silaki

Finally – a good template. I would like to see more.

• Thanks for commenting.. Glad you liked it 🙂

• Angie

Oh wow! This is very helpful. Thank you.

• Thanks for commenting.. Glad you found it useful 🙂

• William Fariss

Employees don’t usually get paid for lunch.A field for “lunch” or other break would be nice thar would subtract from the total worked for that day.

• Thanks for the input William.. makes sense.. I will add this column and update the template soon

• William Fariss

You might need two, One for clock out and one for clock in.

• Prisacariu Cristian

hello , congrats on the project. it is really helpfull , and very nicely done .

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

• Truong cong thanh

Hi all!
I saw some limited in this timesheet cal. If we worked some different shift in the same week in a month
How could we calculate in this template.
Best Regards

• Steve Chase

is there a way to show 3 shifts? example; 7am-4pm (normal hrs), 4pm-11pm (overtime), 11pm-7am (night shift)

• prafull

• Hello Prafull.. You can add employee name at the top of the worksheet (cell D3)

this helped me a lot. but I’m having a problem on the highlighting cause my days are on the upper part. could help me with it? thanks by the way..

• Akane

Hello there. Our regular hours is only 8 hours and the shift starts from 8:00 and ends at 5:00 pm. That’s eight (8) hours in total. But, whenever I try to change the start time from 9:00 AM to 8:AM and the regular hours to 8 hours, it can seem to calculate correctly. Say, an employee started working at 8:00 and ended at 19:00. That should be 8 regular hours and 2 overtime hours. But the template’s result shows 7 regular hours and 3 overtime hours. Can you help me with this?

• Jim-bo

monthly timesheet appears to only allow 29 days.

• Naveed Chaudhri

The monthly timesheet only shows 29 days. The drop down menu shows the correct number of days per month, but to add the data it shows only 29. If you choose a different start date other than the first of the month the 30th shows but the time data shifts.

• Imran Sheikh

hi sumit bansal i have seen your earlier tutorial you are amazing bansal if i put month year name in drop down list and want all input for each employee for monthly wise in a same sheet by just drop down month and select employee … How can we do that please suggest

• Anees Haider

• Helllo Anees.. It’s working for me. Sometimes Excel shows a warning for files downloaded from the web. If that comes up, you need to click on the yellow button for this to work properly.

• Audi Mo

Hello, I want to know how can I add (in 15:27) (out 24:15 ) every time I got error ?? help please

• Özgür BircaN
• Brenny Cakes

thanks for sharing! i have a question about the unpaid breaks column which is not taking off the time from the paid amount?

• Sivasubashini Vijayan

Hi Sumit Bansal

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.

• Sivasubashini Vijayan

Start Time – 7.00
Regular Hours – 8
Regular Pay (hourly) – 9.50
Overtime Pay (hourly) – 14.25
Overtime(Sun) – 19.00
Overtime(PH) – 28.50

• arun V

Hi Sumit, that was a great effort you put. but a quick question why every month end dates (lets say for some months 30 & 31st )appearing in next month’s starting?

• CJ

Hi I have questions about utilizing this for my company–I need to be able to change the start time and do not need the actual pay calculation. Solely for timesheet purposes. However, I am unable to get the formulas to cooperate where I need them to …

I’ve just found this on line and it almost works for my purposes but wondering how I could add in the following parameters

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

• ali ikhlaq

Its awesome save lot of my time to calculate OT. Thank you 🙂

• Michelle Naeder

• It’s in a different tab in the same workbook

• Cabbar Stein

Hi. Thank you for the great template.
Is it possible to have overtime hours calculated including minutes? Currently it seems hours are rounded up/down. I tried to edit the formula but couldn’t get the results I wanted.

• The OT hours are not rounding up. You can multiple the OT value with 60 to get it in minutes.

• Richard Winthrop

Loving your work. It doesn’t quite work when you enter half hours into the regular hour box. I.E regular hours 9.5 starting at 8:15 – 17:45 with a 30 mins lunch. It keeps adding 0.5hours as overtime. Can you fix this please? Thanks

• karan0077

this time calculation is superb but i want month calculation not weekly. thank you

• Karan Patel

this great but i want of monthly not weekly