Night vs day working hours

[Felix Atagong]

I have a spreadsheet that calculates the total working hours of our warehouse staff, overtime, etc...

Cell C1 : starting hour
Cell D1 : ending hour

The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows:
=(D1-C1+(D1<C1))
I need to divide the hours in daytime hours and nighttime hours (nightwork gets more salary). Nightshift starts at 22:00 and ends at 06:00 in the morning. I tried several formulas (MAX, MIN, combined with IF, AND OR) but they never seem to trap all possible situations, especially as midnight makes a switch from 23:59 to 00:01...

The following situations are possible (START - END)

06:00 - 14:00 - person started and ended before nightshift started on the same day
14:00 - 22:00 - person started and ended before nightshift started on the same day
15:00 - 23:00 - person started in dayshift (DS), ended during nightshift (NS) on the same day, 7 DS hours to pay, 1 NS hour.
20:00 - 04:00 - person started in dayshift (DS) day A, ended during nightshift (NS) the next day, 2 DS hours to pay, 6 NS hours.
22:00 - 06:00 - person worked 8 NS hours, zero DS hours...
03:00 - 11:00 - person worked 3 NS hours, 5 DS hours...

Any ideas for a formula? :banghead: Whatever I try there are always some situations that give the wrong results...

[amit_nalawad]

Try this formula, it might help you:hi:
=IF(D1>C1,D1-C1,IF(D1<C1,D1-C1+24,0))

[Bob Phillips]

E2: =MOD(D2-C2,1)-(IF(C2>D2,
MIN(1-C2,TIME(2,0,0))+D2,
MAX(0,TIME(6,0,0)-C2)+MAX(0,D2-TIME(22,0,0))
))

F2: =MOD(D2-C2,1)-E2

[Felix Atagong]

Thanks for the answers. I will check these out.