Exceljet

Quick, clean, and to the point

Calculate days open

Excel formula: Calculate days open
Generic formula 
=IF(ISBLANK(closed),TODAY()-opened,closed-opened)
Summary 

To calculate the number of days a ticket/case/issue has been "open" (i.e. not resolved) you can use a formula based on the IF function, with help from the TODAY and ISBLANK functions. In the example shown, the formula in cell E5 is:

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

When a ticket is open, the result is the number of days since the ticket was opened using the "Opened" date in column C. When a ticket is closed, the result is the number of days between the "Opened" and "Closed" date in column D.

Note: this example was authored on April 18, 2021, so the calculations seen in the screen above are relative to that date.

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.

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 in the past, it will be the 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 simplications 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 in order to display a number, and not a date.

Attachments 
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.