pythonexcelvbagoogle-sheetsexcel-formula

Daytime and Nightime Occurrence Duration of an Event

I want to find the daytime and night-time occurrence duration of an event from its' start time to end time.

The event duration is volatile and can take place for a long time.

I can't figure out a formula.

I am seeking to figure out the duration with a formula or VBA code or Python code.

Link to the sample file with manually calculated duration for each event. The event occurs in multiple scenarios to be considered.

Sample Scenarios

Solution

  • 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)

    enter image description here

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

    enter image description here

    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)