Date calculations

[Guest]

In my spreadsheet I have a column of the date in which we purchased
particular items. In another column, I would like to have a formula that
tells me as of today, how old that item is. For example, let's say I
purchased something 5/24/01, that date is listed in column A. I would like
my formula to be something like =Today() - A1. This would then tell me it
equals 5 years or 5.4 if it was 5 years and so many months, etc. My formula
does not give me the answer I am expecting, it gives me a date. How should I
be entering this formula to get the results I need?

Thanks in advance.

 

[Guest]

Change formatting to general and you will get the number of days, to get
years use

=DATEDIF(A1,TODAY(),"Y")

=DATEDIF(A1,TODAY(),"YM")

used together with the first will give you months after the years have been
taken off



Regards

Peo Sjoblom

 

[David Biddulph]

In my spreadsheet I have a column of the date in which we purchased
particular items. In another column, I would like to have a formula that
tells me as of today, how old that item is. For example, let's say I
purchased something 5/24/01, that date is listed in column A. I would
like
my formula to be something like =Today() - A1. This would then tell me it
equals 5 years or 5.4 if it was 5 years and so many months, etc. My
formula
does not give me the answer I am expecting, it gives me a date. How
should I
be entering this formula to get the results I need?

The formula doesn't give a date it gives a number (number of days); you
have presumably formatted the cell as a date when it should be a number. If
you want it as a number of years you might divide by 365.2422, but of course
that doesn't cope with the differences of leap years.

If you don't want the answer as a number of days, you may want to use the
DATEDIF function (which the help in later Excel variants ignores!). See
http://www.cpearson.com/excel/datedif.htm

 

[Guest]

That worked great! Thanks. Is there also a way to show the years with the
months as a decimal? I could use that in another one.

Thanks again!

 

[Guest]

Sorry, that was stupid - I got it.

That worked great! Thanks. Is there also a way to show the years with the
months as a decimal? I could use that in another one.

Thanks again!