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.
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.
This Tutorial Covers:
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).
Here are the steps to use this Excel Timesheet Template:
A couple of points to keep in mind while using this Excel Timesheet template:
Download the Excel Timesheet Calculator Template
If there is an Excel template you wish existed, let me know in the comments section.
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:
If you are looking for an online timesheet calculator, check this.
FREE EXCEL BOOK
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.
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 Reply
SHAJAN MATHEW How to add the public holidays? Kindly help Reply How to add noon time in and out for timesheet template Reply Emmerentia Strydom Very Helpful. Do you perhaps have a yearly time sheet also. Emme Reply Hugh ColemanCan 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. Reply
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? Reply
Monthly Timesheet, I’ve been trying to get date to display day and month without success. ReplyConsidering this is free, all I can do is say THANK YOU! The template is great and I’m grateful. Reply
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. Reply
Hi. Good Day, Appreciate if you can guide me on how to set checkbox for Public Holiday
Thank you, Reply
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 Reply
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. Reply
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 ? Reply
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? Reply
Do you have this timesheet template compatible with iPad mini 5th generation? Reply MayTae HargeI 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?? Reply
chandra prakaash How to change the amount of total pay and ot value Reply I am accountant ReplyHi 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 Reply
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 . Reply
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 Reply
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. 🙂 Reply
How do I change the rate of pay? Thanks for this great timesheet. 🙂 Reply Mohit yadav Sir please send the salary 2019 excel sheet & pf details also ReplyI 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 Reply
Patti Treschwhen 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 . Reply
I also got the same queries..LoL ReplyIt is possible to add another start time for alternating shifts? one week I work morning and the next I work afternoons Reply
Brandon hayesI’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! Reply
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 Reply
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? Reply
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. Reply
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 Reply
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. Reply
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. Reply
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 Reply
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 Reply
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. Reply
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 Reply
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. Reply
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 Reply
file is corrupted i cant open it. ReplyHi 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? Reply
Kartik Parihar same question is mine Reply Rajatangshu SahaDear 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 Reply
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. Reply
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? Reply
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 Reply
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. Reply
If that is always the case, you could do IN at 07:00 and OUT at 19:00 with a 3-hr break. Reply Ali Ihsan UlusalThe template also does not work with times through multiple days, i.e. someone working from 11pm to 7am during the week. Reply
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. Reply
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. Reply
destia heriyantii 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 Reply
Remove all references to the cells in that column in the formulae. ReplyHi 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 Reply
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. Reply
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? Reply
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! Reply
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. Reply
Paul JohnsonHi, 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 Reply
