Calculating Day Night hours distribution

Greetings fellow Excel lovers,

I'm facing a bit of a conundrum - trying to split shift durations between Day and Night hours, where the night hours are between 22:00 and 06:00 and the output should be in numerical form. The hours I need to be split in two columns - Day hours (all hours in the range of > 06:00 and <= 22:00) and Night hours (> 22:00, <=6:00).

Attached are some simulations of what hours can be seen and what is the desired outcome.

So far I've created a If formula with 7 different conditions, but I'm confident there is a smarter way to do it, I just feel stuck with this.

PS: I searched in the other topics, but couldn't find exactly what I needed, so I'm hoping somebody can help.

I made the following formulas:

Please Login or Register  to view this content.

Column G = Day Hours
Column H = Night Hours

See attached.

WBD

I figured you could hide the columns you didn't need to see. However, here's a solution with just three calculated columns.

WBD

It's based on the formula here for days overlap:

https://exceljet.net/formula/calcula...verlap-in-days

But I often use it for time overlaps.

WBD

Dear Dyakov,

I've simplified the formula, but they are still working correct:
Duration (E2) = 24*(B2-A2+(A2>B2))
Day Hours (F2) =24*MIN(E2/24;B2-J$2+(J$2>B2);I$2-J$2)
Night Hours (G2) = E2-F2

See attachment

Hans Douwe

Dear WBD and Dyakov,

If NH start 12 midnight (or later) you had to change:

I$2-J$2 ---> I$2-J$2+(J$2>I$2)

In this case of I2 = midnight J2>I2 = TRUE:
I$2-J$2+(J$2>I$2) ==> 00:00 - 06:00 + 1 = 18:00

Day Hours (F2) =24*MIN(E2/24;B2-J$2+(J$2>B2);I$2-J$2+(J$2>I$2)

Hans Douwe