Calculate time between three periods

[adhide]

I need to calculate three times during periods that span two different days.

I.e.

1. Hours before midnight;
2. Hours before 6.30 am and
3. if needed the hours after 6.30am

as follows

Start Time Finish time Total Hrs B4 Midnight Decimal Total Hours Between Midnight and 6:30am Decimal After 6:30am Decimal

21:20 7:45 2:40 2.66 6:30 6.5 1:45 1.75


What is the best solutions' an 'if statement' ?

[JBeaucaire]

M1: 6:30 - the early AM cutoff time, for formula reference
A2: 21:20
B2: 7:45
C2: =IF(A2>B2, (1-A2), "")
D2: =IF(C2<>"", C2*24, "")
E2: =IF(A2>B2, MIN($M$1,B2), IF(A2<$M$1, $M$1-A2, ""))
F2: =IF(E2 <> "", E2*24, "")
G2: =IF(A2>B2, IF(B2>$M$1, B2-$M$1, ""), IF(A2<$M$1, IF(B2>$M$1, B2-$M$1, ""), B2-A2))
H2: =IF(G2<>"", G2*24, "")