Calculate day and night hours from a shift based on criteria

[VladiArsenal]

Hi,

I need a formula to calculate how many hours are night hours if the criteria is that Night hours are after 22:00 and before 06:00. Here is a sample shift that I have:

Start Time: 01-09-2020 05:00:00 PM
End Time: 02-09-2020 01:30:00 AM

 

[VladiArsenal]

This is what the table looks like, I need the formula for column D

 

[Tetra201]

If your End Time is always within 24 hours of the Start Time, try the following formula:

=MOD(B2-A2,1)*24-(INT(B2)-INT(A2))*(22-6)-MEDIAN(MOD(B2,1)*24,22,6)+MEDIAN(MOD(A2,1)*24,22,6)

Otherwise, you need a more complex formula.

 

[VladiArsenal]

Yes they are always within 24 hours, the formula works perfectly, thank you very much!

 

[Tetra201]

You are welcome.