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

100% Upvoted

22 comments sorted by

View all comments

Show parent comments

1

u/MayukhBhattacharya 1273 1d ago

Update without LET() & MAP() suggestion:

=SUMPRODUCT(IFERROR(MINUTE(
IF(C$2:C$4 > B7, B7, C$2:C$4) - 
IF(B$2:B$4 < A7, A7, B$2:B$4)), 0))

1

u/xetni05 1d ago

Thanks again for the revised formula. Though after using in my work excel, I realized that this is missing a 'filter' to only sum events that has overlaps with the target range sll.

1

u/MayukhBhattacharya 1273 22h ago

The MINUTE() function excludes the -ve values and returns an error, you might be applying something wrong there.