Age calculation

[Dan B]

Hi,
I'm looking for a formula to give a better response on age calculation. I
have a list of computers with the data they were purchased on. I want to
know how old they are. Here is what I have so far:

G1 contains =today() to give current date
F14 is the purchase date of the first computer in the list
G14 is this formula, which tells me how many months old the computer is:
=($G$1-F14)/30

The problem is that it tells me that the computer is 90 months old, which is
correct, but I want to see that its 7.5 years old. Dividing that by 12
gives the answer, but there are newer computers that are less than a year
old, so how could I have it return the number of years if its older than a
year or the number on months if its less than a year?

Thanks!

 

[Ron Rosenfeld]

Hi,
I'm looking for a formula to give a better response on age calculation. I
have a list of computers with the data they were purchased on. I want to
know how old they are. Here is what I have so far:

G1 contains =today() to give current date
F14 is the purchase date of the first computer in the list
G14 is this formula, which tells me how many months old the computer is:
=($G$1-F14)/30

The problem is that it tells me that the computer is 90 months old, which is
correct, but I want to see that its 7.5 years old. Dividing that by 12
gives the answer, but there are newer computers that are less than a year
old, so how could I have it return the number of years if its older than a
year or the number on months if its less than a year?

Thanks!

One problem in doing this kind of calculation is that years and months are of
differing lengths, so you have to be sure of how you want to define "1 year" or
"1 month". Days and weeks don't have this problem.

There is an undocumented DATEDIF function (see
http://www.cpearson.com/excel/datedif.aspx for details), but it is broken in
the most recent Excel 2007 SP and it is not clear whether it will be fixed.

You could do something like:

=IF((F14+365.25)<$G$1,($G$1-F14)/365.25,($G$1-F14)/(365.25/12))

if that provides sufficient accuracy.
--ron

 

[Dan B]

One problem in doing this kind of calculation is that years and months are
of
differing lengths, so you have to be sure of how you want to define "1
year" or
"1 month". Days and weeks don't have this problem.

There is an undocumented DATEDIF function (see
http://www.cpearson.com/excel/datedif.aspx for details), but it is
broken in
the most recent Excel 2007 SP and it is not clear whether it will be
fixed.

You could do something like:

=IF((F14+365.25)<$G$1,($G$1-F14)/365.25,($G$1-F14)/(365.25/12))

if that provides sufficient accuracy.
--ron

The accuracy is fine, but it doesn't distinguish between months or years.
For example computer 1 was purchased on 5/1/2009, computer 2 was purchased
5/3/2002. Your formula retuned 5.2 and 7.4 respectively. The first is
months and the second is years.
Thanks for the info about the DatedIF function. Sounds like I'm out of luck
on that since its not working. I appreciate your help.

 

[Ron Rosenfeld]

The accuracy is fine, but it doesn't distinguish between months or years.

Well that's simple enough.

Either repeat the test in an adjacent column, (using the IF function) but
output the appropriate label for years or months;

=IF((F14+365.25)<$G$1," years"," months")

or incorporate it into the formula itself:

=IF((F14+365.25)<$G$1,TEXT(($G$1-F14)/365.25," #.0 ""yrs"""),
TEXT(($G$1-F14)/(365.25/12)," #.0 ""months"""))

Which to do depends on whether you need to have the result treated as a text
string, or as a number.
--ron