Calculating time differences in 24 hour time when going past midnight

Hi

I have a sheet detailing employee shift times. This contains their start and end time. Employees receive a standard pay during the hours of 6am until midnight and then receive a bonus pay for hours worked between midnight and 6am.

I need separate formulas to work out how many standard hours were worked during a shift and how many bonus hours were worked during a shift

So in the example shown, the number of standard hours in cell C2 would be 5 (22:00 - 00:00 & 6:00 - 9:00) and the number of bonus hours in cell D2 would be 6 (00:00 - 6:00).

I have a formula for Standard hours when a shift is competed on a single day i.e. 6:00 - 14:00 but not for when a shift goes past midnight.

Explanations of any formulas would be greatly appreciated.

Thank you

Thank you for the replies

First to Mehmetcik, the formula for the bonus hours seems to work fine but the one for the standard hours doesn't seem to work for the shifts that go past midnight.
It returns a result of 2 for the first shift in my example sheet when the answer should be 5.

Secondly to Porucha Vevrku, Your formula seems to work for the first two shifts for the standard hours but then returns a result of 26 for the last one on my sheet. Not sure how that has happened.
Also for the bonus hours it returns a result of 6:00 for each of them when it should be 6,3 and then 0.
I also don’t want the result to be in hours and minutes but hours only. So instead of a shift being 4h:30m it would just be 4.5 hours.

Thank you again for your quick replies. If you know how these could be amended to get the correct results I would appreciate it very much.
I feel they are both very close to what I am asking for and I have included the sheet with your formulas so you can see what results I got, with Mehmetcik on top and Porucha Vevrku underneath.

Originally Posted by Thug

Calculating time differences in 24 hour time when going past midnight

Originally Posted by Thug

...standard ... during the hours of 6am until midnight and then ... a bonus ... for hours worked between midnight and 6am.

... in the example ... the number of standard hours in cell C2 would be 5 (22:00 - 00:00 & 6:00 - 9:00) and the number of bonus hours in cell D2 would be 6 (00:00 - 6:00).

1. Start time = 3.00 => End time = 10.30
Where is "midnight" here ?
Are you suggesting that someone works 31.5 hours continuously ?
E.g. from 03/27/2018, 03:00 => to 03/28/2018, 10:30

2. Start time = 10.00 => End time = 18.00
If it happens within one work day, then where is "midnight" here ?
E.g. from 03/27/2018, 10:00 => to 03/27/2018, 18:00,
or maybe it is
from 03/27/2018, 10:00 => to 03/28/2018, 18:00 => 32 hours of continuous work ?

Specify your criteria more precisely.

I just thought I'd add some that didn't go past midnight to test the formula for all types of shifts i.e one that started in the middle of the bonus hours zone and a normal day shift only including standard hours.

My fault for not explaining that. Sorry.

You can also set it up by ranges using "IF" and "AND", "a small tapeworm" comes out of it (?sorry if it's not in english?), but you can everything to write down exactly.

Porucha Vevrku, Thank you very much. that has worked

Thanks Guys.