Calculate hours worked within a time range

I feel that I have searched the entire internet without much luck, so I sincerely hope that some of you are able to solve this.

I need a formula that returns hours worked if they occur within the range of 00:00 and 06:00 (midnight to 6AM). If they do not occur within the range, it should return 0.

An issue is that starting time is before midnight and ending time exceeds midnight.

In the picture below, the formula should return "6" in cell C5 and "0" in cell C6.


Screen Shot 2017-11-19 at 12.45.24.png

Any advice or guidance will be greatly appreciated.

Try this formula in C5 copied down

=(B5-A5)*24+(B5<A5)*6-MEDIAN(B5*24,6,24)+MEDIAN(A5*24,6,24)

format cells as general or number

The previous formula was the generic format for time periods that cross midnight (I know it didn't quite cross it, but touched it). For daytime time periods the formula is a little simpler

=(B2<A2)*2+MEDIAN(B2*24,6,8)-MEDIAN(A2*24,6,8)

If you want to revise the formula for other daytime periods then the *2 represents the hours in the period and 6 and 8 in the MEDIAN functions obviously the start and end times

Perfect! Thank you for the explanation.