identify differential hours in a shift that goes over midnight

[CanYouDoThis?]

I have a time sheet that includes shift work that goes over midnight. Column B is start time, Column C is end time. I have no problem calculating the number of shift hours , =if (c1>b1,(c1-b1),((c1-b1)+1))

BUT

now I need to identify shift differential hours within that start and end time, and within those shift hours I need to identify those before midnight and after midnight.

Differential hours are from 17:00 hours to 06:00 hours.

If, for example a shift starts at 16:00 hours and ends at 02:00, I need to show:
Total hours worked: 10
Hours of differentials before midnight:7
Hours of differentials after midnight: 2

Can this be done?
Thanks!!

[CanYouDoThis?]

These are hours that are paid at an additional $1 or $2 per hour.

[dogberry]

Hi

Probably a lot better ways of doing this but it answers your problem.

Hope it helps



Chris