How to count working hours between two different periods?

Dear Excelers,

How to count working hours between two different periods?

For example:

I am working in overtime claim form; where I have two different periods, for the reason that every period has a different rate:
1) Period (1) 4:00-21:00, at the rate of 125%.
2) Period (2) 21:00-4:00, at the rate of 150%.


Problem:

Where A2 (On Overtime), and B2 (Off Overtime): A2=20:00, & B2=5:00.

An employee claim over time from: 20:00-5:00, that falls into two periods:
1) From 20:00-21:00, Period (2).
2) From 21:00-4:00, Period (1).
3) From 4:00-5:00, Period (2).

C is Period (1) and D is Period (2).

I want to be: C=7, and D=2.

Is it possible in Excel?!

Thank you so much..

it works...

Dear daddylonglegs,

This formula does not work well, because when I enter an overtime period A2: 16:00, B2: 18:00, 2 hours falls in D2 - period (2) – rather than in C2 - period (1).

Note that:
1) Period (1) 4:00-21:00, at the rate of 125%.
2) Period (2) 21:00-4:00, at the rate of 150%.

But you also said in your original post that if A2 = 20:00 and B2 = 05:00 that C2 =7 and D2 = 2, if you want that the other way round then make the formulas

C2

=((A2>B2)*MEDIAN(0,B2-1/6,17/24)+MAX(0,MIN(7/8,B2+(A2>B2))-MAX(1/6,A2)))*24

D2

=MOD(B2-A2,1)*24-C2