Calculate date overlap in days

In this example, the goal is to create a formula that will calculate the number of days between a start date in column B and an end date in column C that overlap a period defined by date 1 and date 2, which are variables that can be easily changed. You can use a formula like this to do things like:

• Check if one project overlaps another
• Check if a project overlaps a planned holiday
• Check if a task overlaps a period of travel
• Check for overlapping vacation schedules for team planning

The solution explained below involves sorting out the two sets of start and end dates and then performing basic date arithmetic. For convenience only, the "period of interest" is based on two named ranges: (1) date1 (G5) and date2 (G6). This makes the formula slightly easier to read and write.

How Excel handles dates

In Excel's date system, dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, and January 1, 2010 is 40179. The table below shows a few examples of dates and their corresponding serial numbers:

Because dates are just numbers, you can easily calculate durations by subtracting the earlier date from the later date:

=end-start+1

For example, with a start date of 1-Jun-2004 in cell A1 and an end date of 15-Jun-2024 in cell A2, this formula returns 15:

=A2-A1+1 // returns 15

Excel evaluates this formula using date serial numbers like this:

=A2-A1+1
=45458-45444+1
=14+1
=15

Note that we add 1 in a formula like this because we want to include both the start and end dates in the result. When we subtract the start date from the end date, we get the number of days between the dates, excluding the end date itself. By adding 1, we include the end date in the count.

Calculating overlap in days

As explained above, Excel dates are just large serial numbers, so it is easy to calculate the number of days between two dates. The main challenge in this problem is sorting out the start and end dates so that we can calculate an overlap correctly. We do this with the MIN and MAX functions to avoid a more complex formula based on the IF function. In the example shown, the formula in cell D5, copied down, looks like this:

=MAX(MIN(date2,C5)-MAX(date1,B5)+1,0)

At a high level, this is just a variant of the formula to calculate the duration in days mentioned above:

=end-start+1

However, because we have two start dates and two end dates, we need to do a bit more work so that we are subtracting the latest start date from the earliest end date. Working from the inside out, we first calculate an end date:

MIN(date2,C5)

Here, we use the MIN function to find the earlier date between date2 (30-Jun-2024) and the end date in C5 (1-Jul-2024). Effectively, we are calculating the last possible date of the overlapping period because the overlap cannot extend beyond the earlier of these two dates. The result is 30-Jun-2024, since June 30 is earlier than July 1. Next, we calculate a start date like this:

MAX(date1,B5)

This code uses the MAX function to find the later date between date1 (1-Jun-2024) and B5 (1-May-2024). This is the first possible date overlapping period because the overlap cannot start before the later of these two start dates. The result is 1-Jun-2024, since June 1 is later than May 1. Swapping in the values calculated above, we now have:

=MAX("30-Jun-2024"-"1-Jun-2024"+1,0)

Using Excel's native serial numbers for the dates, subtracting the start date from the end date yields 29. To include both the start and end dates in the count, we add 1, and the final result is 30:

=MAX(45473-45444+1,0)
=MAX(29+1,0)
=MAX(30,0)
=30

In cases where there is no overlap in dates, the result after subtraction can be negative, so we use MAX function to force any negative numbers to zero.  In other words, we use the MAX function to trap negative values and return zero instead. Using MAX like this is a clever way to avoid using the IF function.