Forum Discussion
Finding time duration between a start date & time with end date & time
Hi all!
I'm looking for any formula or power query to calculate a total time duration within a day, given the start date, start time, end date, end time. Most of the dates will equal the same but there are some with the end date being the next day. I'd like to be able to exclude any overlaps as well. Currently, I have a large embedded IF formula:
=IF(AND($G4=$O4,$H4<$H5,$P4>=$H5,$P4<$P5,$H4<$H3),$P4-$H4,IF(AND($G4=$O4,$G4>$G3,$H4<$H3,$P4<$P3,$H4<$P3,$P4<$P5),$P4-$H4,IF(AND($G4=$O4,$H4>$H3,$H4>=$P3,$P4>$P3),$P4-$H4,IF(AND($G4=$O4,$H4=$P4),0,IF(AND($G4=$O4,$H4<$P3,$P4<=$P3),0,IF(AND($G4<$O4,$H4<$P3,$P4>$H4),($P4+1)-$P3,IF(AND($G4=$O4,$O4<$G5,$O4<$O5,$H4<$P3,$P4>$P3),$P4-$P3,IF(AND($G4=$O4,$G4>$G3,$H4>$H3,$P4>$P3,$H4>$P3),$P4-$H4,IF(AND($G4=$O4,$G4<$O5,$P4>$P3,$P4>$H5,$H4>$H3,$H4<$H5),0,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3,$P4>$P5,$P2>$P3),$P4-$P2,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$H4<$H5,$P4<$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$H5,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3),$P4-$P2,IF(AND(ISBLANK($O4),ISBLANK($P4)),0,IF(AND($G4<$O4,$H4<$P3,$P4<$H4),($P4+1)-$P3))))))))))))))))
This seems to work for the most part but there are a few that I just can't get. I also pulled up my query and started to enter in the time durations manually and it couldnt come up with anything automatic for me. There must be an easier way for me to do this other than trying to create an IF formula for each answer that turns up incorrect. I have a screen shot below.
4 Replies
=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.
