Author: Oscar Cronquist Article last updated on January 28, 2020

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 whatever, the formula demonstrated here works with everything.

This picture shows two time ranges, 06:00-13:00 (yellow) and 11:00-18:00 (green).

Median function -overlapping time ranges

It is obvious that there are two overlapping hours but how do we calculate it in excel?

The MEDIAN function comes to 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.

Calculate overlapping hours

We have 4 times here to remember, the start and end of time range 1 and 2.

Median function -overlapping time ranges1

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("06:00", "13:00", "11:00") returns 11:00.

And then the end of time range 2.

MEDIAN("06:00", "13:00", "18:00") returns 13:00

13:00 - 11:00 is 02:00. Two hours are overlapping.

The formula becomes

MEDIAN(Start1,End1,End2) - MEDIAN(Start1,End1,Start2)

and returns 02:00.

Lets do something more complicated

Between 00:00 and 08:00 the rate is 8, 08:00-18:00 the rate is 5 and 18:00 to 24:00 the rate is 10.

Median function -overlapping time ranges2

How do we calculate total cost if the time range is 06:00-20:00?

Count overlapping hours for the first range 00:00-08:00 and multiply with rate 8.

=HOUR(MEDIAN("06:00", "20:00", "08:00") - MEDIAN("06:00","20:00", "00:00"))*8

returns 16.

Count overlapping hours for the second range 08:00-18:00 and multiply with rate 5.

=HOUR(MEDIAN("06:00", "20:00", "18:00") - MEDIAN("06:00","20:00", "08:00"))*5

returns 50

Count overlapping hours for the second range 18:00-24:00 and multiply with rate 10.

=HOUR(MEDIAN("06:00", "20:00", "24:00") - MEDIAN("06:00","20:00", "18:00"))*10

returns 20

Median function -overlapping time ranges3

Combining all formulas gives

=HOUR(MEDIAN("06:00", "20:00", "08:00") - MEDIAN("06:00","20:00", "00:00"))*8+HOUR(MEDIAN("06:00", "20:00", "18:00") - MEDIAN("06:00","20:00", "08:00"))*5+HOUR(MEDIAN("06:00", "20:00", "24:00") - MEDIAN("06:00","20:00", "18:00"))*10

returns 86.

Interested in learning more about excel, join my Advanced excel course.

Download *.xlsx file

Median - overlapping ranges.xlsx

I have a question for you

It would be great to build an array formula to shrink the formula above, like this one:

=SUM((MEDIAN(B2:B4,C2:C4,B7+{1;1;1})-MEDIAN(B2:B4,C2:C4,A7+{1;1;1}))*D2:D4)

But it won´t work, you can´t use the MEDIAN function to do that. Do you know a workaround?

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Create a date range [Formula]

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 […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Units contained in a range that overlap another range

This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]

How to AVERAGE time

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]

Days between two dates

The DATEDIF function in cell E3 allows you to calculate days between two dates.