Displaying a decimal as years and months

[Chris]

Hi - need some help here...

Does anybody know how to display a number as years and months?
I would like to show 1.8 as 1 year, 9 months instead of a decimal.

The 1.8 I get by subrtracting my start date from today's date....
=today()-Cell with start date/365

 

[PeteJ]

Have you checked out the "DATEVALUE" function? I think you might be able to
get this to work.

 

[Pete_UK]

You could use DATEDIF in your formula to derive the answer in years
and months directly - Chip Peasron explains how here:

http://www.cpearson.com/excel/datedif.aspx

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hi - need some help here...

Does anybody know how to display a number as years and months?
I would like to show 1.8 as 1 year, 9 months instead of a decimal.

The 1.8 I get by subrtracting my start date from today's date....
=today()-Cell with start date/365

Forget the 1.8.

Do this:

=DATEDIF(Start_Date,TODAY(),"y") & " years, " &
DATEDIF(Start_Date,TODAY(),"ym") & " months"

See http://www.cpearson.com/excel/datedif.aspx for documentation of this
function. It is present in Excel for many years (at least since 1995) but only
documented in 2000.
--ron

 

[reitanospa1]

You could try something like this:
=TRUNC((TODAY()-B7)/365.25)&" year, "&MOD(((TODAY()-B7)/365.25),
1)*12&" months"

I don't know the Datedif function and I can't find a reference to it
in help in Excel 2003, but it would make for a prettier formula.
Is Datedif part of toolpack?

 

[Ron Rosenfeld]

You could try something like this:
=TRUNC((TODAY()-B7)/365.25)&" year, "&MOD(((TODAY()-B7)/365.25),
1)*12&" months"

I don't know the Datedif function and I can't find a reference to it
in help in Excel 2003, but it would make for a prettier formula.
Is Datedif part of toolpack?

No, it is a native function in Excel, and has been present at least as far back
as 1995, maybe further (I don't have my documentation handy).

It is only in Excel HELP for 2000, but the function itself is present in most
other versions.

See http://www.cpearson.com/excel/datedif.aspx for documentation.
--ron

 

[Les]

Chris,
I don't know what version of Excel you are using, but I am using Excel 2007
and it does not include the "DATEDIF" function. When I put that function into
the help search field, it showed other ways to get what you want using the
Month and Year functions. While DATEDIF sounds neat, it is not in my version
of Excel. However, the methods it showed worked very nicely.

It is in a section named "Calculate the difference between two dates" and it
shows you how to calculate the number of days, the number of months, and the
number of years between 2 dates.

I think that will give you what you want.

 

[David Biddulph]

You've said that Excel 2007 help doesn't mention DATEDIF, but have you tried
using the function in a formula, Les? I expect you'll find it works.

For reasons unknown, DATEDIF has been omitted from help in Excel versions
apart from 2000, though the function is there.
In the absence of help, details are at
http://www.cpearson.com/excel/datedif.aspx

 

[Les]

David,
I did try to use DATEDIF in a formula, but I got the #NAME? when Excel did
not recognize the function. I have the Enterprise version of MS Office, so it
should be in there... but, alas, it is not.

If you think of something else for me to try I will, but for now it looks to
me like the function is not included in Excel 2007.

Thanks,
Les

 

[Ron Rosenfeld]

David,
I did try to use DATEDIF in a formula, but I got the #NAME? when Excel did
not recognize the function. I have the Enterprise version of MS Office, so it
should be in there... but, alas, it is not.

If you think of something else for me to try I will, but for now it looks to
me like the function is not included in Excel 2007.

Thanks,
Les

Les,

I just upgraded to Office 2007 (Standard) and DATEDIF is, indeed, present.

With dates in A1 & A2, I used this formula:

=DATEDIF(A1,A2,"md")

and did NOT get a NAME error.

Perhaps if you copy and paste here exactly what you are entering, and exactly
what is in your precedent cells, someone might be able to figure out the issue.
--ron

 

[David Biddulph]

You haven't fallen into the trap of mis-spelling the function name have you?
It's DATEDIF (with one F) in Excel and DATEDIFF (with 2 Fs) in VBA.

 

[Chris]

Thank you - this was the answer!!!!

You could use DATEDIF in your formula to derive the answer in years
and months directly - Chip Peasron explains how here:

http://www.cpearson.com/excel/datedif.aspx

Hope this helps.

Pete

 

[Chris]

Hi - just found that Datedif works - it is part of the ToolPak install. Thank
you for your help!!!!

 

[Chris]

Thank you - this was the answer!!!!

Forget the 1.8.

Do this:

=DATEDIF(Start_Date,TODAY(),"y") & " years, " &
DATEDIF(Start_Date,TODAY(),"ym") & " months"

See http://www.cpearson.com/excel/datedif.aspx for documentation of this
function. It is present in Excel for many years (at least since 1995) but only
documented in 2000.
--ron

 

[David Biddulph]

I think you'll find that DATEDIF is *not* part of the Analysis ToolPak, but
is a native Excel function.