Search code examples
excelexcel-formulaformuladate-arithmetic

How to calculate night and day working hours

I have to build Excel formula which should calculate day and night working hours. We have 2 column - start and end time:

 start      end
16:00:00   02:00:00

Major condition is to separate working hours by day and night - lets assume that day hours are from 08:00:00 until 20:00:00, night working hours are from 20:00:00 until 08:00:00

I need the result into two new columns - night hours and day hours For above example it should be:

day hours  = 4
night hours= 4

How to build more flexible and universal excel formula?

Solution

  • add more colume with end time = minmum of 20:00:00 and the real end time.

    add more colume with start time = maximum of 08:00:00 and the real end time.

    now you have night time only

    (do the same for day time)