I am keeping track of cases which have an open date, and may or may not have a closing date. I want to have a column that reflects the age of the case. Therefore, if the case is open, the age of the case will be today's date minus the open date. However, if the case is closed, the age will be the closed date minus the open date. How can I set up the formula to determine whether to use today's date or the closed date?

Thanks

Hi, Here is an example:

A B C 1 Opened Closed Duration 2 05-Jan-10 26-Feb-10 52 3 05-Jan-10 68 This example run on 14-Mar-10

The formula in cell C2 is=IF(B2<>"",B2-A2,TODAY()-A2)

The formula is just dragged down to extend it.The formula uses IF() to test if the cell containing the closed date is empty. If it is empty then the calculation is today's date - start date, but if there is data in the closed date cell, then the difference between the two dates is calculated.

Regards

Keep in mind that the TODAY() function is volatile and will recalculate every time the worksheet recalculates. That means that every time you open the worksheet and then try to close it, it will ask you if you want to save the changes, even if you made absolutely no changes.

I only bring this up so that you'll know why that is happening.

Thanks to both of you! Works perfectly! I made one small addition to the formula: I divide the whole package by 365, to get the result in years rather than days.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History