Sum time value within a certain time range

Hello,


I am trying to get my excel sheet to automatically count the "normal" day hours and the "evening" hours, as they have different pay values.
My question is, if there a way to get excel to calculate the two separate times, within that one shift, which crosses over from the day to evening shift.
the evening hours are from 1900 to 0700.

For example:

a shift starts at 06:00 and ends at 12:30
This shift counts 1 hour of "evening" pay and 5,5 hours of "day" pay.


shift starts at 12:00 and ends at 21:00
This shift then counts 7 hours of "day" pay and 2 hours of "evening" pay...

is there any way of doing it automatically?

Hi FDibbins! thank you for your quick response!

I entered the formulas into my spreadsheet, it works, but, once the times 06:00 to 12:00 are added in, then the normal time shows 5 and the OT shows 6. However, the OT should only show 1 and the rest should be under the normal time..

Same goes for 06:00 to 20:00, the "OT" time should only be 2, yet it shows 7, and the normal time is reduced to zero..


hope you can fix it! :D