Calculate days open

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.