Search code examples
pythonexcelvbagoogle-sheetsexcel-formula

Daytime and Nightime Occurance Duration of an Event

I am working on a project where I have to find the daytime and night-time occurrence duration of an event from its' start time to the end time of the event. The event duration is volatile and can take place for a very much long time. The main problem is I can't figure out the one correct formula till now even tried Gpts and AI to evaluate a correct one. The following is the sample file that have manually calculated and correct duration for each event. The event occurs in multiple scenarios that needs to be considered. https://docs.google.com/spreadsheets/d/1ZfeFb6sDovh2zL09bEUvw2MavojZmj2l/edit?usp=sharing&ouid=102160894337947446820&rtpof=true&sd=true

I am seeking out some help from Stack Overflow Community to help me figure out the formula or VBA Code or Python Code to achieve the correct output duration. Please help me on this.

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)