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 may be dates, times, or any numerical range, the formula demonstrated here works with everything.
This picture shows two time ranges, 06:00-13:00 (yellow) and 11:00-18:00 (green).
It is obvious that there are two overlapping hours but how do we calculate how much they overlap in Excel?
The MEDIAN function comes to the rescue, but first, let me explain the function. It returns a value that separates the higher half of a data set from the lower half. Example, MEDIAN(1,2,3) returns 2. 1 is the lower half and 3 is the higher half.
MEDIAN(1,2,3,4,5,6) returns 3.5 because there are two values (3, 4) separating the higher half (5,6) from the lower half (1,2). The average of these two values is 3.5.
What's on this webpage
1. Calculate overlapping hours
We have 4 times here to remember, the start and end of time range 1 and 2.
Let see what happens if we use the MEDIAN function with the start and end value of time range 1 and only the start value of time range 2.
MEDIAN("6:00 AM", "01:00 PM", "11:00 AM") returns 11:00 AM.
And then the end of time range 2.
MEDIAN("6:00 AM", "01:00 PM", "06:00 PM") returns 01:00 PM
01:00 PM - 11:00 AM is 02:00. Two hours are overlapping.
The formula becomes
and returns 02:00.
2. Calculate total cost based on different rates per hour
The rate is 8 between 12:00 AM and 08:00 AM, 08:00 AM - 6:00 PM the rate is 5 and 6:00 PM to 12:00 AM the rate is 10.
How do we calculate total cost if the time range is 06:00 AM - 8:00 PM?
Count overlapping hours for the first range 00:00-08:00 and multiply with rate 8.
returns 16.
Count overlapping hours for the second range 08:00-18:00 and multiply with rate 5.
returns 50
Count overlapping hours for the second range 18:00-24:00 and multiply with rate 10.
returns 20.
Combining all formulas gives
returns 86.
2.1 I have a question for you
It would be great to build an array formula to shrink the formula above, like this one:
But it won't work, you can't use the MEDIAN function to do that. Do you know a workaround? See next section.
3. Calculate total cost based on different rates per hour (smaller formula)
The image above demonstrates a formula that calculates the total cost based on rates per hour. Check out Alex Grobermans formula in the comments section below.
The formula above won't work if you start and end spans over multiple days, see next section below.
Explaining calculation in cell C10
Step 1 - Calculate hour
The HOUR function returns an integer representing the hour of an Excel time value.
HOUR(C7)
becomes
HOUR(42005.25)
and returns 6.
Step 2 - Create cell reference
The INDIRECT function creates a cell reference based on text values.
INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))
becomes
INDIRECT("A"&6+1&":A"&12)
becomes
INDIRECT("A"&7&":A"&12)
becomes
INDIRECT("A7:A12")
and returns A7:A12.
Step 3 - Create row numbers
The ROW function returns row numbers from a cell reference.
ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8)))
becomes
ROW(A7:A12)
and returns {7; 8; 9; 10; 11; 12}.
Step 4 - Calculate frequency based on time intervals
The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.
FREQUENCY(data_array, bins_array)
FREQUENCY(ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))), HOUR(D3:D4))
becomes
FREQUENCY({7; 8; 9; 10; 11; 12}, {8;18})
and returns {2; 4; 0}.
Step 5 - Multiply with rates and return a total
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT(array1, [array2], ...)
SUMPRODUCT(FREQUENCY(ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))), HOUR(D3:D4)), E3:E5)
becomes
SUMPRODUCT({2; 4; 0}, E3:E5)
becomes
SUMPRODUCT({2; 4; 0}, {8;5;10})
and returns 36.
4. Calculate total cost based on different rates per hour across days
Alex Groberman contributed with an interesting formula, check out that comment below. I modified that formula and came up with this in order to get it working with a range that spans over multiple days.
Array formula in cell C10:
The formula above works with all Excel versions, the formula below is smaller, however, it works only in Excel 365:
Explaining calculation in cell C10
Press with left mouse button on the image above to see a larger version. The image shows the time range from start (cell C7) 1/1/2015 6:00 AM to end (cell C8) 1/1/2015 12:00 PM.
Interested in learning more about excel, join my Advanced excel course.
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Dates basic formulas category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]
Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]
The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to […]
The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
The DATEDIF function in cell E3 allows you to calculate days between two dates.
The image above shows the DATEDIF function calculating the number of complete months between two dates. Column B and column C […]
This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Time category
Table of Contents How to AVERAGE time How to enter an array formula Explaining formula How to AVERAGE time hh […]
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
8 Responses to “How to calculate overlapping time ranges”
Leave a Reply
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.
Great post! Here's a solution, which uses a couple of (changeable) assumptions:
=SUMPRODUCT(FREQUENCY(ROW(INDIRECT("A"&HOUR(A7)+ 1&":A"&HOUR(B7))), HOUR(C2:C3)), D2:D4)
By quantizing the time (into hours in this case), and then counting the quantity in each rate bucket, we can then do a SUMPRODUCT against the rates. Remember that the FREQUENCY function returns one more item than the number of buckets passed to it, so I left out the final bucket time to compensate.
-Alex
Alex,
fantastic, why didn´t I think of that.
Thank you for your valuable comment!
I'm willing to take my medicine if there's something simple in here that I should have seen, but here goes...
How do I find overlapping ranges (using the MEDIAN function) for three to six time ranges? Or do I have to figure each range separately, then combine it into one IF statement using AND statements?
Forgot to add something to my previous post:
There are a variable number of time range(s) within each day - anywhere from 0 to 4 time ranges - and the number of ranges may change from day to day. Any formula must be able to handle this.
Henry Dishington,
Do you want to find overlapping ranges? Read this:
https://www.get-digital-help.com/2013/11/26/identify-overlapping-records/
Or do you want to count overlapping days?
Another solution at your quest, with CSE formula:B2:B4,$A$7,B2:B4)>0,IF($B$7B2:B4,$A$7,B2:B4),0)*D2:D4*24)
=SUM(IF(IF($B$7
for some unknown reason, previous paste was an error.
Correct formula is:
{=SUM(IF(IF($B$7A2:A4,$A$7,A2:A4)>0,IF($B$7A2:A4,$A$7,A2:A4),0)*C2:C4*24)}
O, no, not again!
I try to upload a file.