‘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:
- Calendar Integrated with a To Do List.
- Excel To Do List (4 Templates).
- Task Matrix Productivity Template.
- Holiday Calendar Template (US Only).
- Project Management – Employee Leave Tracker.
- Shared Expense Calculator.
- Vacation Itinerary and Packing List Template.
If you are looking for an online timesheet calculator, check this.