Calculate Date Overlap In Days

Explanation

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.

Explanation

In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still open. Because dates in Excel are just serial numbers , the math is quite simple. We can simply subtract the “Opened” date from the date today, which we calculate with the TODAY function. However, when a ticket is closed, we need a way to stop this calculation so that the “Days open” number doesn’t keep increasing.

Calculating days open with IF

The core operation of this formula is controlled by the IF function . In pseudo-code, we can write something like this:

=IF(ticketIsOpen,daysSinceOpened,daysSinceClosed)

After we translate this into a proper Excel formula, the formula in cell E5, copied down, is:

=IF(ISBLANK(D5),TODAY()-C5,D5-C5)

To check if a ticket is open, we use the ISBLANK function :

ISBLANK(D5) // is date closed empty?

This is a logical test that returns either TRUE or FALSE. If the result is TRUE, the ticket is still open. In that case, we run a calculation based on the TODAY function to calculate the number of days the ticket has been open:

TODAY()-C5 // days since opened

TODAY returns the current date from which we subtract the date opened. Both dates are serial numbers . As long as the date opened is past, it will be a smaller number. The number of days open will keep increasing as time goes on because the TODAY function will continue to return larger numbers.

If the result from ISBLANK is FALSE, the ticket is closed. In that case, we run a calculation to figure out how many days the ticket was open before being closed:

D5-C5 // days open before closed

Here, we subtract the date opened from the date closed. Once an issue is closed, the result is always the same and doesn’t change.

Simplification

One thing you might notice is that we subtract the open date regardless of whether we use today’s date, or the date closed. In other words, -C5 appears in both arguments. This means we can make a slight simplification and use a formula like this:

=IF(ISBLANK(D5),TODAY(),D5)-C5

In this version, we use the IF function only to return the date to start with, then subtract the start date in C5. We can even go further, and replace the IF function altogether with the MIN function :

=MIN(D5,TODAY())-C5

Here we use MIN to get the smaller of two values: the closed date, or the date today. If the closed date is empty, its value is zero, so the date today will be used. If the closed date is not empty, it’s smaller (or equal) to today, and the close date will be used.

Both simplifications are a good example of how formulas in Excel can be often adjusted to reduce length and redundancy, at the risk of becoming more cryptic and less intuitive to the average user.

Note: The result in column E is formatted in the “General” number format to display a number and not a date.

Conditional formatting for open tickets

The workbook also has one conditional formatting rule applied to highlight rows where tickets have not been closed. The formatting is applied with this formula:

=ISBLANK($D5)

Notice that because we want to highlight the entire row, we need to lock the reference to column D. To create a rule like this:

1. Select the range B5:E16.
2. Home > Conditional Formatting > New rule.
3. Use a formula to determine which cells to format.
4. Enter the formula “=ISBLANK($D5)” in the input area.
5. Select the formatting of your choice.
6. Click OK to save the rule.

Video: How to apply conditional formatting with a formula