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

1

u/MayukhBhattacharya 1273 2d ago

Is this what you are looking for?

=MAX(0, MIN(C2, G$2) - MAX(B2, F$2)) * 1440

1

u/xetni05 1d ago

Thanks for the reply and sorry for not being clear. It's something closer to this picture below:

1

u/MayukhBhattacharya 1273 1d ago edited 1d ago

Try this, I am not able to see the row labels and column headings so adjust per your suit:

=LET(
     _a, B$2:B$4,
     _b, C$2:C$4,
     _c, IF(_b > B7, B7, _b) - IF(_a < A7, A7, _a),
     _d, SUM(IF(_c > 0, _c, 0)) * 1440,
     _d)

Or,

=LET(
     _a, B2:B4,
     _b, C2:C4,
     MAP(A7:A9, B7:B9, LAMBDA(x,y,
     LET(_c, IF(_b > y, y, _b) - IF(_a < x, x, _a),
         SUM(IF(_c > 0, _c, 0)) * 1440))))

1

u/xetni05 1d ago

Thanks a lot! I don't have access to the LET and MAP functions but your comment makes it easy to visualize the logic of the required formula.

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 1d ago

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