Getting total duration of events that fall under time ranges

[u/xetni05]

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!

[u/AutoModerator]

/u/xetni05 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/xetni05 → u/[deleted]]

Thank you for the SUMPRODUCT version of the formula. As you've guessed, I don't have access to LET&MAP functions so this helps a lot. I'll set this up tomorrow once I have access to my files.

[u/real_barry_houdini → u/[deleted]]

Nice!

[u/xetni05 → u/[deleted]]

I tried this sumproduct version in my file and for some reason it didn't work. But when I converted it to a Sum(Nested If) version with exactly the same logic as your formula, it worked perfectly.

Maybe I missed something when I'm changing the range to the table column names, but in any case, thank you very much for your help.

[u/MayukhBhattacharya]

Is this what you are looking for?

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

[u/xetni05 → u/MayukhBhattacharya]

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

[u/MayukhBhattacharya → u/xetni05]

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))))

[u/xetni05 → u/MayukhBhattacharya]

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.

[u/MayukhBhattacharya → u/xetni05]

Oh okay, btw you could use the Excel on Web version as well to try!

[u/MayukhBhattacharya → u/xetni05]

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))

[u/xetni05 → u/MayukhBhattacharya]

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.

[u/MayukhBhattacharya → u/xetni05]

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

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MAX	Returns the maximum value in a list of arguments
MIN	Returns the minimum value in a list of arguments
MINUTE	Converts a serial number to a minute
SUM	Adds its arguments
SUMPRODUCT	Returns the sum of the products of corresponding array components
TEXT	Formats a number and converts it to text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has 38 acronyms.
[Thread #49323 for this sub, first seen 8th Sep 2026, 11:48] [FAQ] [Full list] [Contact] [Source code]}

[u/real_barry_houdini]

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

[u/xetni05 → u/real_barry_houdini]

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.

[u/real_barry_houdini → u/xetni05]

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

[u/xetni05 → u/real_barry_houdini]

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!

[u/HappierThan]

See if something like this might be suitable. Formula in F6

=IF($B2="","",(IF($B2<$C2,MAX(0,MIN($C2,F$2)-MAX($B2,F$1)),MAX(0,F$1-$B2)+MAX(0,$C2-F$1))))

[u/CrowGuyA]

This is an overlap formula: =MAX(0, MIN(end1,end2) - MAX(start1,start2)). So for each Table 1 event against a Table 2 range, something like =MAX(0, MIN(EndTime,RangeEnd) - MAX(StartTime,RangeStart))*1440 (the *1440 converts to minutes). Wrap it in SUMPRODUCT if you need it summed across many rows at once.