If you find it hard to calculate NightTime, I suggest you calculate DayTime first, and use TotalHourse – DayTime for night time, then you wouldn’t need to worry about “crossing midnight”.

So, for day time duration, here is the formula (in K10)

=(INT(E10)-INT(D10))*(F10-G10)-MEDIAN(MOD(D10,1),G10,F10)+MEDIAN(MOD(E10,1),G10,F10)

For night time duration:

=E10-D10-K10 (in L10)

Finally, remember to format your cell as [h]:mm:ss:

Explaining the formula:

1. think of INT(E10)-INT(D10) as number of days between start date and end date. (including start date, but excluding end date).

2. INT(E10)-INT(D10) * (18 -6) can be interpreted as number of dayTime hours from start date to end date. (start date inclusive, end date exclusive)

3. To get number of hours between 12/09/2024 7:00 and 13/09/2024 16:00, we need to deduct extra hours from start date, and add extra hours to the end date. i.e we need to deduct (7:00-6:00) from 12/09, and add (16:00 -6:00) for 13/09.
   that is, (13/09/2024 - 12/09/2024) * (18 -6) -(7-6) + (16-6) is the number of dayTime hours between 12/09/2024 7:00 to 13/09/2024 16:00.
   It can be simplified to (13/09/2024 - 12/09/2024) * (18 -6) + 16 -7

4. If start time on start date is outside DayTime Hours,
   e.g for 12/09/2024 4:00 to 13/09/2024 16:00, we just treat it as ifstart date starts at 6:00. i.e (13/09/2024 - 12/09/2024) * (18 -6) - (6-6) + (16 - 6) = (13/09/2024 - 12/09/2024) * (18 -6) + 16 -6

5. If end time on end date is outside DayTime hours, e. for 12/09/2024 4:00 to 13/09/2024 19:00, we just treat it as if end date ends at 18:00. i.e (13/09/2024 - 12/09/2024) * (18 -6) - (6-6) + (18 - 6) = (13/09/2024 - 12/09/2024) * (18 -6) + 18 -6

6. Finally, 4 & 5 can be simplified with -MEDIAN(start hour,6,18)+MEDIAN(end hour, 6, 18)

you can treat this problem in the same way as finding the intersection interval between two lines(one of the lines being your daytime duration)[subtracting from total time duration gives you night time], you can check for the code online to have some insights. What is important here is implementing logic for when the start and end day are not the same, then you’d have to calculate separately for the end and start day and add a (constant daytime time*number of days between those 2 days).
This is my first response to any query, apologies if my help was not of use.
Best of luck for your task!

Verbose, spill formula (Microsoft 365), not as compact and elegant as rachel’s:

(Almost) following the logic in the “DayNight” sheet:

=LET(
    last_data_cell, XLOOKUP(TRUE, G10:G500 <> "", G10:G500, , , -1),
    shift_data, D10:last_data_cell,
    shift_hours, LAMBDA(acc, shift_row,
        LET(
            start, INDEX(shift_data, shift_row, 1),
            end, INDEX(shift_data, shift_row, 2),
            night_start_hour, INDEX(shift_data, shift_row, 3),
            day_start_hour, INDEX(shift_data, shift_row, 4),
            total_hours, (end - start) * 24,

            day_hours, IFS(
                end <= (INT(start) + day_start_hour),
                0,
                DAYS(end, start) = 0,
                IF(
                    start > (INT(start) + night_start_hour),
                    0,
                    MIN(end, INT(end) + night_start_hour) -
                        MAX(start, INT(start) + day_start_hour)
                ),
                DAYS(end, start) > 0,
                (
                    DAYS(end, start) *
                        (night_start_hour - day_start_hour)
                ) -
                    IF(
                        start > (INT(start) + day_start_hour),
                        start - INT(start) - day_start_hour,
                        0
                    ) +
                    IF(
                        end > INT(end) + day_start_hour,
                        MIN(end, INT(end) + night_start_hour) -
                            INT(end) - day_start_hour,
                        0
                    )
            ) * 24,

            VSTACK(
                acc,
                HSTACK(day_hours, total_hours - day_hours, total_hours)
            )
        )
    ),
    REDUCE(
        {
            "Day Time Duration (Hours)",
            "Night Time Duration (Hours)",
            "Total Duration"
        },
        SEQUENCE(ROWS(shift_data)),
        shift_hours
    )
)