Since I have started this blog, the Excel timesheet calculator template has been one of the most requested templates.
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.
Frequently Asked Questions (FAQs)
Since I get a lot of queries about using this timesheet template, I thought of creating this FAQ section to answer some of your queries. If you have got a question, you can ask me in the comment section, but I would request you to go through this section first.
Q: I only see the Weekly timesheet template. Where are the bimonthly and monthly templates? Ans: All three timesheet templates I provided as separate tabs. You will find the other templates when you click on the tab for the template (tabs have been named accordingly). Q: What if I want to track the timing of multiple employees using this timesheet template. How do I do it? Ans: this template is made for one person per sheet. If you need to track multiple employees or team members, you need to create multiple sheets in that case. Q: Can I have multiple sheets being recorded in the same template? Ans: This template is made for one time-shift only - which you can specify by mentioning the start time and the number of regular hours. Q: Can I print this timesheet template? Ans: Yes, these timesheet templates have been made to fit a single page when printed. You can go to File and then clic on Print, or use the keyboard shortcut Control + P. This will open the Print preview page. Q: In monthly timesheet template, when I select February 1 as the start date, why does it still show me dates from the next month? Ans: The monthly timesheet is made to cover 31 days in total. So it will show you 31 days, starting from the date that you have specified.
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.
151 thoughts on “Excel Timesheet Calculator Template [FREE DOWNLOAD]”
Hi.. I have build a table to handle the progress of working hrs every 3 hrs, and I am interested to make it work in VBA.. for example: shift starts 7am-3pm.. OT is From 3pm-6pm at 1.5 times, 6pm-9pm 1.75 times, 9pm-11pm at 2 times.. but when the shift starts 11pm
How to add the public holidays? Kindly help
How to add noon time in and out for timesheet template
Very Helpful. Do you perhaps have a yearly time sheet also. Emme
Can you do an excell spreadsheet to find amounts owed for the ‘flexable’ workweek method of paying overtime?
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.
I’ve been trying to make a timesheet for my work, so my overtime gets paid to me.
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?
Monthly Timesheet, I’ve been trying to get date to display day and month without success.
Considering this is free, all I can do is say THANK YOU! The template is great and I’m grateful.
The calculation is absolutely interesting but have question to asked Alphonse.
1.what if there is no Overtime in the work schedule and there is no Sat&Sunday included what is the formulae needed.
2. what if you want to add an amount for in the formulae different from an hourly rate e.g Hourly Rate is =25$ and overtime is half of that amount how do you fixed it into the formulae.
Hi. Good day,
Why are 9:00-9:15=0.3?
I need it to be 0.25…
Hi. Good Day,
Appreciate if you can guide me on how to set checkbox for Public Holiday
Thank you,
Hi,
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
The time sheet shows overtime hours after a seven hour day, but it should not generate overtime hours until after 8 hours in a day. And, actually, overtime is not paid until one works more than 40 hours a week, even if one works 10 hours a day for four days, overtime is not paid in the USA. I can’t imagine how complicated the formula would be to get this working properly because to account for potential work days for a seven day week, the calculations have to start on a Sunday and end on Saturday. It can get even wilder when some people may have a work week that starts on a Thursday and ends on a Friday. That would take a lot of gymnastics to have a time sheet with that added flexibility. LOL. Good luck with that.
HI there, I had a look at you timesheet calculator and couldn’t figure out if there was a way of using this for flexible shift start & finish times ?
How do i add days from Example January month end, ended the 25 January now i have to add the 26 till the end of the month on February time sheet on top? With out messing up the whole excel sheet?
Do you have this timesheet template compatible with iPad mini 5th generation?
I desperately need to be able to add a second frozen title row that has “tabs” that will filter my table/column one (if it were dates) by weekly, pay period, monthly, yearly and all. Maybe have to dat ranges in a block at the bottom and a way to scroll through them to the next selection of dates. Is this at all possible?!?!
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??
How to change the amount of total pay and ot value
I am accountant
Hi All
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
How i can update auto attendance using project spent hours.
i fill data like information in template
after i change month … Still date n days are changed but data will fill as it is
Data will not changed … How to change data with changed month .
Can the timesheet be converted to calculate overtime once 40 hours have been worked?
What a great Excel Sheet – saves me allot of time. Thank you for this!!!
Hi,
This template is extremely good but would it be possible to have a daily sheet with mutliple clock in and clock out possibilities? I have searched the internet for days trying to find a compatible template and this is the best one I have found :-).
My work is very flexible with differing start and finish times and take breaks when the work allows, it would be great if a daily sheet was available that also automatically filled in the week and month sheet as well.
I would be very greatful if you can help in any way.
Nick Scott
Hi,
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..!
🙂
How do I change the rate of pay?
Thanks for this great timesheet. 🙂
Sir please send the salary 2019 excel sheet & pf details also
I wish there were excel template(s) in workbook for capturing both cash and credit transactions to Produce Profit and loss accounts, Account Receivable,account payable Closing stock( Stock inventory at Close) and Balance sheet
when entering night hours ie working 10 pm – 5 am for example. I figured out that it is 22:00 to 0:00, then the next day is 0:00 to, 5:00, but what do I do for that night that starts again at 10 pm Would I have to insert twice? Hopefully you understand what I am trying to say .
I also got the same queries..LoL
It is possible to add another start time for alternating shifts? one week I work morning and the next I work afternoons
I’m new to excel and work for a company that makes it difficult to figure your pay, a friend made something similar to yours but I find yours is easier. Thank you!
employee_ident first_name middle_name last_name manager_first_name manager_middle_name manager_last_name date start_time end_time worked break training lunch
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 17:25:51 19:29:22 2:03:32 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 19:29:23 19:49:01 0:00:03 0:19:36 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 19:49:02 20:49:03 1:00:02 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 20:49:04 21:07:35 0:00:03 0:18:29 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 21:07:36 21:42:16 0:34:41 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 21:42:17 21:56:50 0:00:03 0:14:31 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 21:56:51 23:14:35 1:17:45 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 23:14:36 23:22:00 0:00:03 0:07:22 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/1/2019 23:22:01 23:59:59 0:37:59 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/2/2019 0:00:00 1:12:18 1:12:19 0:00:00 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/2/2019 1:12:19 1:18:23 0:00:03 0:06:02 0:00:00 0:00:00
2923196 Pranay Chauhan Saurabh Singh 5/2/2019 1:18:24 2:35:32 1:17:09 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/2/2019 13:09:10 14:05:37 0:56:28 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/2/2019 14:05:38 14:22:31 0:00:03 0:16:51 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/2/2019 14:22:32 15:55:43 1:33:12 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/2/2019 15:55:44 16:25:49 0:00:03 0:00:00 0:00:00 0:30:03
968340 Pulkit Khurana Krishan . 5/2/2019 16:25:50 18:01:00 1:35:11 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 13:17:27 14:15:59 0:58:33 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 14:16:00 14:30:51 0:00:03 0:14:49 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 14:30:52 15:59:21 1:28:30 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 15:59:22 16:21:59 0:00:03 0:00:00 0:00:00 0:22:35
968340 Pulkit Khurana Krishan . 5/3/2019 16:22:00 17:33:38 1:11:39 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 17:33:39 17:47:47 0:00:03 0:14:06 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 17:47:48 21:25:09 3:37:22 0:00:00 0:00:00 0:00:00
968340 Pulkit Khurana Krishan . 5/3/2019 21:46:18 22:32:35 0:46:18 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/13/2019 21:33:10 23:59:59 2:26:50 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 0:00:00 1:13:16 1:13:17 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 1:13:17 1:38:18 0:00:03 0:00:00 0:00:00 0:24:59
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 1:38:19 4:17:35 2:39:17 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 4:17:36 4:21:46 0:00:03 0:04:08 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 4:21:47 4:44:46 0:23:00 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 4:44:47 5:07:38 0:00:03 0:22:49 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 5:07:39 6:09:10 1:01:32 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 6:09:11 6:13:36 0:00:03 0:04:23 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 6:13:37 6:46:01 0:32:25 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/14/2019 21:29:01 23:59:59 2:30:59 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 0:00:00 0:21:17 0:21:18 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 0:21:18 0:25:50 0:00:03 0:04:30 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 0:25:51 1:32:18 1:06:28 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 1:32:19 1:59:34 0:00:03 0:00:00 0:00:00 0:27:13
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 1:59:35 4:20:44 2:21:10 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 4:20:45 4:46:21 0:00:03 0:25:34 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 4:46:22 5:52:30 1:06:09 0:00:00 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 5:52:31 5:56:42 0:00:03 0:04:09 0:00:00 0:00:00
3247723 Srishti Kharbanda Navdeep Varshney 5/15/2019 5:56:43 6:34:33 0:37:51 0:00:00 0:00:00 0:00:00
Wrong place to add your times.
I really like this time sheet. My only concern is that when entering the break hours it will round up or down. Example: 45 minute break should be 0.75, but when I enter that it changes to 0.8. Is there a way to correct this?
Hi! Thanks for the template.
Our regular start time is 10:00 AM, but some employees time in is earlier. Like 9:30 AM sometimes 8:00 AM and their time out is exactly when they reached the 11 regular hrs. But there is still appearing OT hours even they only work for 11hrs. Thanks in advance.
I downloaded the excel timesheet calculator, It works fine and great job
I my office my weekend is Sunday, but my office works for 5 hours in saturday and also i need to have sunday overtime in a separate column, can you help me in sort it out
I know there is a probability that many of the questions that I answered are old and that they will never reach the original baffled poster of each question… can’t tell because there are no dates, neither the article’s publishing date nor the comments’ posting dates. However, in looking for answers myself on sites around the net, I have found many solutions that were posted several years prior to me finding it. I assume that someone will probably come around in the future asking themselves the same questions, and I hope they read before they ask.
Cheers.
Hi, and Thanks for your site as a whole, for it has provided several answers through time.
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.
There is only one shift in and out, could you please add 2 shift in and out.
I would be very great if you provide me
Hi
I have x1.0 , x1.5 , x2.0
These rates change throughout the day. Is it possible to apply this to the sheet?
Mon – fri
5:00 – 7:30 = x1.5
7:40 – 16:30= x1.0
16:30 – 21:00 = x1.5
21:30 – 5:00 = x2.0
Sat
– 5:00 = x2.0
5:00 – 12:00 = x1.5
12:00 sat – mon 5:00 = x2.0
It is very helpful but I have 1 problem. my company counts OT HRS only after completion of regular HRS ex. regular hrs are 8 and after 8 hrs OT starts. It cannot be like 7 regular hrs and 1 OT hrs. can you please tell how to do it. A lot of thanks.
The formulae for Regular and OT pay have to be modified.
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
Hi. I want the monthly time sheet to pay overtime only after 195 hours for the month including sunday work. I want to also have to pay a certain overtime for sundays. And also pay certain days as public holidays as overtime.
The weekly sheet has some errors in the formula. it shows OT when there should be none. and the Reg Hour total lacks 1 count
file is corrupted i cant open it.
Hi Sir,
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?
same question is mine
Dear Sir,
You are “GREAT” and I Salute you. Your tutoring made me a huge difference than previous and now I feel really confident in all of my Excel works. I’m much better than previous because of you and I feel like I can conquer anything!
Again, Thank you sir and if I have money than I could attain all the training courses provided by YOU.
I wish you endless success and happiness in the coming year 2019! Take care of yourself and your family members and best of luck!
Truely,
Rajatangshu Saha
Thank Sir,
Your training online is helping me understand a lot how to use excel. The eBook I received will give me great knowledge to use. I wish you safe travel to Mumbai and we meet again in 2019.
How do I add months to the monthly timesheet and keep the same formulas?
Can i use this to calculate changeover times for a proccess in manufacturers
In Texas, overtime isnt calculated until 40 hours of strait time has been reached….it is not calculated after 8 hours a day…how can i fix this?
One way to do it without a major overhaul is to let it calculate the alleged weekly OT. Then, at the end of the week, down where it sums the columns up, you could add to the formula the verification that if the total Reg Hrs is greater than 40, then Reg Hrs – 40 is the OT. The only time it will not be true is if the employee does not work a full 8-hr day one or more days in the week. Then it will not look right, though the totals will be right, and so will the pay.
Example
RH OT
8 2
8 3
3 0 (went home sick)
0 0 (full sick day)
8 0
======
32 0
In these irregular cases,
if RH < 40 … if RH + OT < 40 … RH + OT = Reg pay, no OT
if RH < 40 … if RH + OT = 40 … RH pay = 40, no OT
if RH 40 … RH pay = 40 and OT pay = RH + OT – 40
HTH… Cheers
I love this time card template. hours. However I need this same template to include split shifts. Example: 7 am – 10 am; 11 am – 2 pm; 4 pm – 7 pm. Is this possible? Please advise. Thank you for your time and assistance.
If that is always the case, you could do IN at 07:00 and OUT at 19:00 with a 3-hr break.
The template also does not work with times through multiple days, i.e. someone working from 11pm to 7am during the week.
Yes, you are right, it does not work for that situation.
You have to spit it up to entries on two days…
23:00 to 24:00 on one day and 00:00 to 07:00 on the next day, but at the end of that 2nd day you would need to enter 23:00 to 24:00 again, and the problem is that here you cannot use two lines or rows for that same date.
This is where a daily two-shift time sheet would work.
This is very useful if you are using fixed days. I have a customer using a weekend period starting at friday 7pm and ending at monday 7am, which has a higher payout. I am trying myself to program such a calculation, haven’t been able to solve it yet. Any help on this would be appreciated.
i want to put duration work time and delete the break hours because my company pay for break time but when i delete break hours it all changed
Remove all references to the cells in that column in the formulae.
Hi Sumit,
I Really like this spreadsheet, one of the best around!
Question;
I live in Australia, so our days are ahead of the US by one.
How to change the formula string to suit OZ?
=DATE($C$9,MATCH($D$9,Data!$B$2:$B$13,0),’July 2018 Timesheet’!$E$9)
I.e. Need to have Saturday July 1st not Sunday July 1st
Am learning about excel formulas but can’t see how to do this change.
Cheers,
Damian
Damian, I imagine that by now you have found an answer to your dilemma. However, in order to avoid anyone else getting confused, I will answer your question.
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.
thanks
Hi,
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?
In the Total Pay formula there is a check to see if the day is on the established weekend,
IF(AND(ISNUMBER(SEARCH(TEXT(B14,”ddd”),$E$10)),Data!$F$2),$I$10,$H$10)
add that to the Regular Hours formula and if it is true then 0, so
IF(AND(ISNUMBER(SEARCH(TEXT(B14,”ddd”),$E$10)),Data!$F$2), 0 ,$H$10)
Also add it to the OT formula and if it is true then in place of the 0 write the references to
(OUT – IN – (8/24) – (break/24)) * 24
Don’t forget to add the corresponding parenthesis at or toward the end of the formula. Mine is too altered to share it and have it make any sense, but it starts like this
=IF(AND(D14″”,E14″”),IF(AND(ISNUMBER( … … … *24,0) ) ,0)
and the closing parenthesis being the “spaced-out” one. 😀
HTH… Cheers!
I thought this was good until I could not get the drop down box to work in the Monthly Time sheet only the Date one goes to drop down list from the data sheet.
Hi,
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
How do I put enter 1/2 hour for lunch?
0.5
How did you get the total pay?
Please advise
Thank you
How can I change the rate on sunday?
how do i use drop down for more than one employee.if i want to add more employees name
You can only use one timesheet per employee…
However, if you list all the employee names in a column in the DATA sheet, then you can use Data Validation on the Employee Name on the Timesheet so you don’t have to type it on the employee’s timesheet.
Cheers.
I don’t understand the function of the start time when they have a clock in and out time.
my employees have different start times every day. please help
The function of the Start time, as I see it, is that it helps determine if someone has done a No-No and entered an IN time from one day and an OUT time from the following day, as over-night shifts usually go, and that is because the over-night shifts cannot be recorded on these timesheets.
Cheers
thank you for sharing this useful sheet. but how can i introduce the (latency in) as COLUMN if a worker come late to work and i want to punish him by deducting one hour ?
Check if IN is greater than Start
hi
my job time start it 13:00 and out time is 01:30
then kese me is main entry karoun ??????
my e-mail
bakhtawarshezad812@gmail.com
can we calculate minus OT in this
This seems to be a pretty good template, and starting point. I am probably going to Modify it to have it split out into multiple projects. I am a Drafter so each project i work on has to be billed to, I can go from a single project in a week to twenty projects in a week that has to be billed to. So that is a project for myself, but if you were looking at a possible add to this template that would be useful to those that are in the same situation as I am.
ok, i give up. How do I change the company name at the top of the spreadsheet template, it just says [Company Name] and I can not figure out how to edit this.
go to cell b2
Hi Samit, My team regularly work 12+ hr days (06:00-16:00 Normal time…$25/hr…with 30min break, then the next 3hrs would be paid at 1.5 x normal rate…25 x 1.5 = $37.5…and any time after is paid a 2 x normal…25 x 2 = $50). Is there a way to have two different OT rates and therefore two different OT columns?
In answer to your question, yes, it can be done. I’m sorry I cannot provide you with the formulae needed, but you basically have the logic already worked out.
06:00 to 16:00 is only 10 hrs and you stated 12+ hr days, so
let’s say 06:00-20:00, or 14 hrs from IN to OUT.
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!
Hi, how do I populate 5 people filling different timesheets? I am interested in the total working hours.
Thanks
what a GREAT document!!!! my only issue is I get 45minutes break hours and the macro will not allow this, either as 0:45 or 0.75. it only allows 0.7 or 0.8hrs
Could you tell me how to allow this please?
thanks again!
Mark
If you enter 0.75 in a cell and Excel shows you 0.8 that is only due to formatting… but it will use 0.75 to calculate, as can be seen on the formula bar.
Cheers
This is excellent. The only thing is I don’t want the starting time. How do I remove it? Also I need an extra overtime column
This is great! I would love it if you had an option for different shifts to add, with the pay rate difference to enter as well.
Hi,
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
Appreciate if you could assist in adding additional 2 more in time and out time formulas. Would you mind to share the formulas please ? Thank you.
This is very helpful. In Our country we use two different overtime calculations:
Normal overtime at a rate of x1.5 and
Sundays and Public Holidays at a rate of x2.0
Can you add it to your timesheet for me to download Please.
Great work, I really liked it. Very helpful.
Thanks for commenting Garry.. Glad you found it useful!
How would you accommodate if you came in late and staying late to make up for hrs (assuming 8hr/day with 30min no paid lunch)
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…
I love this format so much … is that possible to do the cost price list using this format ?
Do you have a version of this with two types of overtime for example time and half and double time?
I’m looking for a formulated Excel spreadsheet that calculates anything over 8hrs but less than 10hrs is time-and a half and anything over 10hrs is double time
Hello Angela.. The template can only accommodate one level of overtime pricing. I don’t have one for tiered overtime pricing.
can you tell me . i have multiple employees .there is space for only one employee name . is there any drop down option where i can add my employee names and choose according to there
Hello Shubham.. The template is made for one employee, however, you can create multiple sheets for different employees.
HELLO …thank you very much for your excellent templates…………do you have a yearly template so we can log employees total hours worked for the weeks and 12 months and then totals at the bottom for the financial year ………….. their holidays taken…. unpaid leave taken….. public holidays high lighted …sick leave balance of same …and a separate section for the total gross wages tax ….net wages …superannuation again weeks/month and then total for year at the bottom ..etc so one template per year contains all necessary information to comply with all rules and regulations
Good Day can you please help me I love your template for the Time sheet, but what must i do if the person does not have a fixed starting time, I see that this is messing with the figures?
Please help Urgently
Very handy. We made a collection of free excel timesheets templates specifically for construction and field service companies (where work happens at many sites) using a lot of the same info you show here. The biggest difference with ours is including job and task info because these types of companies switch that up throughout the day and need the info for job costing as well as payroll. You can check them out here if you want: https://www.clockshark.com/Blog/timesheet-templates-collection/
We run a retail chain and the ovrtimes are difficult to calculate, especially for the weekends when the guys are working one weekend on and one off, to add to this we work the saturday as normal time due to closing off early on Friday, would it be too complicated to modify this for my purpose so its automated?
this great but i want of monthly not weekly
Monthly timesheet is also there is the template (3rd tab)
ty
this time calculation is superb but i want month calculation not weekly. thank you
Monthly timesheet too is in the template
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
Thanks for letting me know Richard.. I have fixed the template.
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.
What about a bi-weekly timesheet?
It’s in a different tab in the same workbook
Its awesome save lot of my time to calculate OT. Thank you 🙂
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
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 …
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?
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.
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
thanks for sharing! i have a question about the unpaid breaks column which is not taking off the time from the paid amount?
very cool. I used daily time tracker. from http://www.someka.net/product/employee-time-tracker-excel-template/
Brother, i downloaded this software. Drop down menu of Year, Month and Weekend not working
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.
Hello, I want to know how can I add (in 15:27) (out 24:15 ) every time I got error ?? help please
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
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.
monthly timesheet appears to only allow 29 days.
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?
You may use the below Formula for 8 hours calculation.
=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
Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin
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..
where to add employee name?
Hello Prafull.. You can add employee name at the top of the worksheet (cell D3)
is there a way to show 3 shifts? example; 7am-4pm (normal hrs), 4pm-11pm (overtime), 11pm-7am (night shift)
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
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
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
You might need two, One for clock out and one for clock in.
Oh wow! This is very helpful. Thank you.
Thanks for commenting.. Glad you found it useful 🙂
Finally – a good template. I would like to see more.
Thanks for commenting.. Glad you liked it 🙂
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 🙂
Can you pleeeeeeease help me with a excell template for my boss. He owns his own paint shop/Spray Booth. He need a template that he can jot down Paint Formulas on and retain them for future reference on future jobs. Ive asked him to help me create it and hes not interested so Im left with imagining what kind of table template i should make. I can have him fill in the blanks just need a table i guess.
please email me at streamlinepainting@att.net