Calculating overtime and regular time based on shift schedule.

Hello All:

I am trying to calculate over time based on shift time.

For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.

Anything on Saturday or Sunday should be calculated as OVERTIME as well.


I have a few different formulas, but I can not seem to get it right. Please help!

Thank you in advance for any help.

try these....

for regular time
=IF(AND(A5<TIME(7,0,0),B5<TIME(7,0,0)),0,IF(B5>TIME(15,0,0),TIME(15,0,0),A5)-IF(A5<TIME(7,0,0),TIME(7,0,0),B5))
for OT
=IF(AND(A5<TIME(7,0,0),B5<TIME(7,0,0)),B5-A5,IF(A5<TIME(7,0,0),A5-IF(B5<TIME(7,0,0),B5,A5),0)+IF(B5>TIME(15,0,0),B5-TIME(15,0,0),0))

ok, i found a small error in my formula for OT...
=IF(AND(A5<TIME(7,0,0),B5<TIME(7,0,0)),B5-A5,IF(A5<TIME(7,0,0),TIME(7,0,0)-IF(B5<TIME(7,0,0),B5,A5),0)+IF(B5>TIME(15,0,0),B5-TIME(15,0,0),0))

not sure what the "0.33" and "0.37" in post 5 are tho, and please be more specific about what didnt work?

The 0.33 and 0.37 were the results from the formula you provided.

I added "*24" at the end and it seems to be working better for the OT.

I am still have problems with the REG Hours. I changed it to "=D5-F5" and it seems to be ok.

Also if you look at line 11 on sheet "v2" if the time rolls into the next day it doesn't calculate properly.

Please see below screenshot and attached revised spreadsheet.


Thank you for your help.


ts ss2.jpg
Shift - REG Time v OT - rv1.xlsx

see the attached.

I dont see how you're 2nd up;load is different to the version I sent you, but change the formula in G5 to...
=IF(B5<=TIME(7,0,0),0,IF(AND(A5<TIME(7,0,0),B5<TIME(7,0,0)),0,IF(B5>TIME(15,0,0),TIME(15,0,0),A5)-IF(A5<TIME(7,0,0),TIME(7,0,0),B5)))

lol ok, try this 1...
=IF(B5<TIME(7,0,0),0,IF(AND(A5<TIME(7,0,0),B5<TIME(7,0,0)),0,IF(B5>TIME(15,0,0),TIME(15,0,0),A5)-IF(A5<=TIME(7,0,0),TIME(7,0,0),B5)))

i have revised things a bit and created range names for the start and end times (makes the formulas easier to read). i think i have it nailed now, please test and report back.

regarding weekends, you dont yet have anywhere to indicate dates, but im sure we can build networkdays() into the formulas

Ok, I'm back on this. I had a few things pop up.

Where did you make your modifications? On your original upload?