How to sum overlapping time

Author: Oscar Cronquist Article last updated on February 01, 2023

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the number of overlapping minutes.

The formula uses the earliest and latest date and time value in column B and C as the range to count overlapping minutes.

What's on this webpage

Sum overlapping time
1. Sum overlapping time based on a date range
Sum overlapping time - Excel 365
Get Excel file

1. Sum overlapping time

Array formula in cell C10:

=SUM((MMULT(TRANSPOSE($B$3:$B$6^0), (TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440)>=$B$3:$B$6)*((TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440))<$C$3:$C$6))>1)*1)

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell C10

The INDEX function allows you to create an array of values, in this case, minute intervals.

MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440

returns

{43101.25, 43101.2506944444, 43101.2513888889, 43101.2520833333, 43101.2527777778, 43101.2534722222, 43101.2541666667, 43101.2548611111, 43101.2555555556, 43101.25625, 43101.2569444444}

Now check if these minute intervals are between or equal to each date and time range.

(TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440)>=$B$3:$B$6)*((TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440))<$C$3:$C$6)

returns

{1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0; 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0; 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0}

The MMULT function allows you to add these values column by column.

MMULT(TRANSPOSE($B$3:$B$6^0), (TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440)>=$B$3:$B$6)*((TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440))<$C$3:$C$6))

becomes

MMULT(TRANSPOSE($B$3:$B$6^0), {1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0; 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0; 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0})

becomes

MMULT({1,1,1,1}, {1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0; 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0; 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0})

and returns

{1, 2, 3, 3, 2, 2, 2, 2, 1, 2, 0}

The following picture shows the array and what the MMULT function returns.

A value larger than 1 indicates an overlapping time value.

becomes

{1, 2, 3, 3, 2, 2, 2, 2, 1, 2, 0}>1

and returns

{FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE}.

Lastly, multiply with 1 to convert boolean values to numerical values and then sum the numbers.

SUM((MMULT(TRANSPOSE($B$3:$B$6^0), (TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440)>=$B$3:$B$6)*((TRANSPOSE(MIN($B$3:$B$6)+(ROW(A1:INDEX($A:$A, (MAX($C$3:$C$6)-MIN($B$3:$B$6))*1440+1))-1)/1440))<=$C$3:$C$6))>1)*1)

becomes

SUM({FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE}*1)

becomes

SUM({0,1,1,1,1,1,1,1,0,1,0})

and returns 8.

1.1 Sum overlapping time based on a date range

The picture above shows you a different setup, it allows you to use a smaller range than the min and max date and time in B3:C5.

Formula in cell C14:

=SUM((MMULT(TRANSPOSE($B$3:$B$5^0), (TRANSPOSE($B$9+(ROW(A1:INDEX($A:$A, ($C$9-$B$9)*1440+1))-1)/1440)>=$B$3:$B$5)*((TRANSPOSE($B$9+(ROW(A1:INDEX($A:$A, ($C$9-$B$9)*1440+1))-1)/1440))<$C$3:$C$5))>0)*1)

The ROW function limits the use of these formulas, if you have a range larger than 1,048,576 minutes, which is the same as the number of rows in a worksheet, you will need another solution than the one presented here.

Get Excel *.xlsx file

How to count overlapping timev3.xlsx

2. Sum overlapping time - Excel 365

This Excel 365 formula works just like the formula in section 1.1 above, however, the SEQUENCE and LET functions simplify and shorten the formula considerably.

Dynamic array formula in cell C10:

=LET(y, B3:B5, x, B9+SEQUENCE(, (C9-B9)*1440+1, 0)/1440, SUM((MMULT(TRANSPOSE(y^0), (((x)>=y)*((x)<C3:C5)>0)*1)>0)*1))

Explaining formula

The date and time values are actually numbers, dates are whole numbers. 1 is 1/1/1900 and the next day 1/2/1900 is 2 and so on. Decimal numbers are time values in Excel, for example:

00:00 AM is 0 (zero)
12:00 PM is 0.5

The whole number and the decimal number together form a date and time value in Excel. 1.5 is 1/1/1900 12:00 PM.

Step 1 - Subtract numbers

The minus sign lets you subtract numbers in Excel formulas, this example calculates a number representing the difference between the two date and time values specified in cells C9 and B9.

C9-B9

becomes

43101.2569444445 - 43101.25

and returns 0.00694444450346055

Step 2 - Multiply with 1440

The asterisk lets you multiply numbers in an Excel formula. There are 60 minutes in one hour, and twenty-four hours in one day. 60*24 equals 1440.

(C9-B9)*1440

becomes

0.00694444450346055*1440

and returns

10.0000000849832

Step 3 - Add 1

The plus sign lets you add numbers in an Excel formula.

(C9-B9)*1440+1

becomes

10.0000000849832+1

and returns

11.0000000849832

Step 4 - Create a sequence of numbers

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(,(C9-B9)*1440+1,0)

becomes

SEQUENCE(,11.0000000849832,0)

and returns

{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}.

SEQUENCE(,(C9-B9)*1440+1,0)/1440

becomes

{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}/1440

and returns

{0, 0.000694444444444444, 0.00138888888888889, 0.00208333333333333, 0.00277777777777778, 0.00347222222222222, 0.00416666666666667, 0.00486111111111111, 0.00555555555555556, 0.00625, 0.00694444444444444}

Step 5 - Add sequence to start date

B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440

becomes

43101.25 + {0, 0.000694444444444444, 0.00138888888888889, 0.00208333333333333, 0.00277777777777778, 0.00347222222222222, 0.00416666666666667, 0.00486111111111111, 0.00555555555555556, 0.00625, 0.00694444444444444}

and returns

{43101.25, 43101.2506944444, 43101.2513888889, 43101.2520833333, 43101.2527777778, 43101.2534722222, 43101.2541666667, 43101.2548611111, 43101.2555555556, 43101.25625, 43101.2569444444}

Step 6 - Check if values in array is larger or equal to each value in cell range B3:B5

The larger than sign and the equal signs are logical operators, they return TRUE or FALSE if a condition is met or not.

(B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5

becomes

{43101.25, 43101.2506944444, 43101.2513888889, 43101.2520833333, 43101.2527777778, 43101.2534722222, 43101.2541666667, 43101.2548611111, 43101.2555555556, 43101.25625, 43101.2569444444}>={43101.2506944444; 43101.2513888889; 43101.25625}

and returns

Step 7 - Check if values in array is smaller to each value in cell range B3:B5

(B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5

becomes

{43101.25, 43101.2506944444, 43101.2513888889, 43101.2520833333, 43101.2527777778, 43101.2534722222, 43101.2541666667, 43101.2548611111, 43101.2555555556, 43101.25625, 43101.2569444444}<{43101.2520833333; 43101.2548611111; 43101.2569444445}

and returns

{TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

Step 8 - Multiply arrays (AND logic)

The asterisk character lets you multiply numbers in an Excel formula, the equivalent function would be the PRODUCT function.

((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)

becomes

{FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}*{TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

and returns

{0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1}.

When you multiply boolean values TRUE or FALSE the result is their numerical equivalent:
TRUE = 1
FALSE = 0 (zero)

Step 9 - Check if result is larger than 0 (zero)

((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0

becomes

{0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1}>0

and returns

{FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}.

Step 10 - Rearrange vertical values to horizontal values

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(B3:B5^0)

becomes

TRANSPOSE({43101.2506944444; 43101.2513888889; 43101.25625}^0)

becomes

TRANSPOSE({1; 1; 1})

and returns

{1, 1, 1}.

Step 11 - Convert boolean values to their numerical equivalents

(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1

becomes

and returns

{0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1}

Step 12 - Calculate the matrix product of two arrays

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

Function syntax: MMULT(array1, array2)

MMULT(TRANSPOSE(B3:B5^0),(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1)

becomes

MMULT({1, 1, 1},{0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1})

and returns

{0, 1, 2, 1, 1, 1, 1, 0, 0, 1, 1}

Step 13 - Check if result is larger than 0 (zero)

MMULT(TRANSPOSE(B3:B5^0),(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1)>0

becomes

{0, 1, 2, 1, 1, 1, 1, 0, 0, 1, 1}>0

and returns

{FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE}.

Step 14 - Convert boolean values to numerical equivalents

(MMULT(TRANSPOSE(B3:B5^0),(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1)>0)*1

becomes

{FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE}*1

and returns

{0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1}.

Step 15 - Add numbers in array and return a total

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM((MMULT(TRANSPOSE(B3:B5^0),(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1)>0)*1)

becomes

SUM({0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1})

and returns 8.

Step 16 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

SUM((MMULT(TRANSPOSE(B3:B5^0),(((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)>=B3:B5)*((B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440)<C3:C5)>0)*1)>0)*1)

y - B3:B5
x - B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440

LET(y,B3:B5,x,B9+SEQUENCE(,(C9-B9)*1440+1,0)/1440,SUM((MMULT(TRANSPOSE(y^0),(((x)>=y)*((x)<C3:C5)>0)*1)>0)*1))

Count values category

Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

See all articles in 'count values' category

Overlapping category

Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

See all articles in 'overlapping' category

Functions in this article

SUM MMULT TRANSPOSE MIN ROW MAX LET

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form