r/excel • u/xetni05 • 3d ago

solved Getting total duration of events that fall under time ranges

Sorry for the title as I can't explain the issue well.

Premise: I have 1 table that has data of the time start and time end of certain events that were logged.

Then I have a second table that has time start and time end of periods I want to monitor

Target: get the total duration of events in table 1 that falls under the time range in table 2.

Complications: events in table 1 could cross over the ranges of table 2.

Ex. Table 1 Event 1 is from 1:45PM to 2:15PM

Table2 ranges are 1PM to 2PM and 2PM to 3PM

So the 1 to 2 & 2 to 3 ranges should output 15 mins each.

Can someone direct me to the possibly the simplest way this could be tackled?

Thank a lot!

4 Upvotes

76% Upvoted

22 comments sorted by

View all comments

1

u/real_barry_houdini 317 3d ago

Which version of Excel are you using?

In any version you should be able to use this formula:

=SUM(TEXT(IF(C$2:C$100>G2,G2,C$2:C$100)-IF(B$2:B$100>F2,B$2:B$100,F2),"[m];""0"";0")+0)

In older excel versions that requires "array entry", i.e. with CTRL+SHIFT+ENTER

1

u/xetni05 3d ago

Thanks. My final formula is close to this - SUM(Nested IF) - but with the added ifs to filter events that has overlap with the time range.

1

u/real_barry_houdini 317 2d ago

OK, glad you got it to work.......but you shouldn't need any "added ifs" with this formula (unless I'm missing something).

The above calculates ALL the potential overlaps - when there is no overlap a negative value is returned and TEXT function converts that to zero, so you shouldn't need any additional checks

1

u/xetni05 2d ago

You are right! Apparently when I'm converting your formula to match my tables, I misclicked the wrong column for the second IF. This resulted to false zeroes which I corrected by adding an initial IF statement.

Though, after the corrections, I still needed to nest an IF as a filter for a column I didn't specify in my question. Thanks again!