This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello!
I'm looking for some assistance with calculating formulas on a timesheet
The In Time and Out Time is recorded for each employee and we need the sheet to calculate the total Regular hours, swing hours and night hours worked in that time period.
The regular hours are any hours worked between 07:00 and 15:00
Swing hours are any hours worked between 15:00 and 23:00
Night hours are any hours worked between 23:00 and 07:00
There also needs to be overtime hours calculated for each of the 3 types of hours. Overtime is only calculated once the persons total hours hit 40 for the week and from that period onwards every hour they work gets automatically considered as overtime. Categorised within the time periods that they work.
Several people have had a go at this and have had no success. The hardest part being the night shift as it doesn't seem to want to calculate overnight either that or it gives me negative values
Any help would be appreciated!
Thanks!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
=IF(M2<40,0,M2-40)
For night hour, you may use two formula for 23:00-24:00 and 0:00-7:00 then sum them.
Certainly.
Here is what i have at the moment
| In Time | Lunch Start | Lunch End | Lunch Total | Out Time | Regular Hrs | OT Hours | Swing Hrs | Swing OT Hours | Night Hours | Night OT Hours | Total Hours (Day) | Total Hours (Accumulative) | Total Hours (OT) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 08:00:00 | 12:00 | 12:30 | 00:30:00 | 18:47:00 | 7.00 | 3.78 | 10.76 | 10.76 | |||||
| 13:00 | 13:00 | 13:30 | 00:30:00 | 23:00 | 2.00 | 8.00 | 9.98 | 20.74 | |||||
| 17:00 | 00:00:00 | 22:00 | 0.00 | 5.00 | 5.00 | 25.74 | |||||||
| 03:00 | 00:00:00 | 23:00 | 8.00 | 8.00 | 16.00 | 41.74 | |||||||
| 07:00 | 00:00:00 | 15:00 | 8.00 | 0.00 | 8.00 | 49.74 | |||||||
| 00:00:00 | 0.00 | 0.00 | 0.00 | 49.74 | |||||||||
| 00:00:00 | 0.00 | 0.00 | 0.00 | 49.74 |
I am looking for a formula to go in OT hours, Swing Overtime hours, night hours and night overtime hours.
the column OT Hours should only calculate if the accumulative hours hits 40 hours. from that point onwards any hours worked will automatically be considered as overtime and categorised by the shift. So if they had hours worked between 07:00 and 15:00 and the 40 hour threshold had been hit then it would be OT Hours for however many hours they worked between that time period.
Same concept for swing OT hours except it's calculated between the hours of 15:00 and 23:00
So going by the data in the spreadsheet, if the formula is correct Swing OT Hours should have 1.74 in I5 and OT Hours should have 8.00 in G6
Night hours are a bit more tricky and that's what ive been having the most trouble with as im not sure how to factor in across the formulas on the spreadsheet if the out time is on the next day from the in time. Perhaps factoring in a date but i don't know how to do that and not mess it all up.
Night shift is between 23:00 and 07:00 and follows the same concept as the others. Regular night hours calculated until the 40 threshold and then its Night OT
Currently using this for Regular hours
=IF(OR(A2="", E2=""), 0, MAX(0, (MIN(E2, TIME(15,0,0)) - MAX(A2, TIME(7,0,0)))) * 24)
and this for Swing hours
=IF(OR(A2="", E2=""), 0, MAX(0, (MIN(E2, TIME(23,0,0)) - MAX(A2, TIME(15,0,0)))) * 24)
Could you share some dummy data and expetced result? Then I can give formula suggestions on it.