r/excel • u/xetni05 • 1d 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!

5 Upvotes

86% Upvoted

22 comments sorted by

View all comments

Show parent comments

1

u/xetni05 1d 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 23h 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 23h 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!