Forum Discussion
Finding time duration between a start date & time with end date & time
=MOD(enddate+endtime-(startdate+starttime),1)
While I appreciate your reply, your formula gives the duration of the event. I am looking to get the entire duration for a single day, excluding overlaps. As you can see in my screen shot, a single day may have several events spanning from 00:00 to 23:59. The first event is on December 31, 2024 starting at 0751 - 0805; while the second event is the same day but at 0759 - 0902. You can see that there is an over lap between 0759-0805, so the total daily duration would only count these two events in the day being from 0751 - 0902 (total duration being 1:11) then continuing on again same day at 1048 - 1155, being no overlap it would add 1:07 to December 31 total daily duration.
Please provide a sample workbook with the expected results.
I am unable to upload a sample file. The screen shot is my data and AF is my desired outcome column.
Any line that have the same date in the Date and Date.1 columns, I have the total duration being end time - start time. BUT if there is a secondary 'event/line' in the same date that has an overlapping time, I dont want it to count this time twice.
i.e. one event is from 6:52 to 9:25, making that total event duration 2:33
the next event is from 8:02 to 8:43, making that total even duration 0:41.
because the second event happens DURING the first event, the second event isnt counted. And if it only overlapped for a portion of the first duration, it would not count the overlap, only the amount of time outside of the overlap.
