Excel alternative formula

Dell Optiplex 760
March 14, 2010 at 08:12:22
Specs: Microsoft Windows XP Professional, 2.793 GHz / 3291 MB
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?


See More: Excel alternative formula

March 14, 2010 at 10:13:09

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

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.


Report •

March 14, 2010 at 19:48:37
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.

Report •

March 14, 2010 at 20:37:49
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.

Report •
Related Solutions

Ask Question