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