Excel - Day and Night Hours

[Nikhil R]

Hi guys,

So I thought I'd done it. I'm so close yet so far. Maybe just a tweak here and there.

Basically I've got a table where it has Start Time (C1), End Time (D1), Total Hours (E1), Day Hours (F1) and Night hours (G1). And I have Day Shift (J1) with 06:00 on (J2) and 20:00 on (J3).

This is my code for Total Hours: =IF(D2<C2,1,)+D2-C2
This is my code for Day Hours: =MAX(,MIN($J$3,IF(D2<C2,1,)+D2)-MAX(C2,$J$2))
This is my code for Night Hours: =E2-F2

Now all is working when I input day shift, however upon entering a night shift which is anything from 20:00 - 06:00, it inputs everything onto the night hours rather than splitting it.
It also doesn't take into account for example if I put in 19:00 into start time and 07:30 into End Time, it inputs 1 hour into Day Hours and then 11:30 into Night hours which is incorrect as cut off point is 6am.

I tried to copy the Day Hours code and change accordingly but to no avail

Any assistance would be much appreciated.

 

[Nikhil R]

I've uploaded an image so you know what I'm talking about, as reading can be a tad confusing at times as to what you're wanting to achieve.

Thanks in advance