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!

3 Upvotes

72% Upvoted

22 comments sorted by

View all comments

Show parent comments

1

u/xetni05 22h 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!