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. The core operation of this formula is controlled by the IF function. In pseudo code, we can write something like this: After we translate this into a proper Excel formula, the formula in cell E5, copied down, is: To check if a ticket is open, we use the ISBLANK function: 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 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: 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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
