Displaying a decimal as years and months

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
Have you checked out the "DATEVALUE" function? I think you might be able to
get this to work.
 
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
Click to expand...

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
 
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?
 
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?
Click to expand...

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
 
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.
 
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
 
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
 
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
Click to expand...

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
 
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.
 
Hi - just found that Datedif works - it is part of the ToolPak install. Thank
you for your help!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help displaying odd numbered month 3
Excel Vba to change displayed year automatically. 14
Age Formula 12
find out specific month and year 1
How do I calculate age from birthdate to present in Excel? 4
Date formula resulting in Year, Months & days 3
Location Oriented Formula 1
Excel Formula for years and months 2

Back
Top