How do I convert a number into Years, Months, Days format?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to convert a number and display it as Number of Years, Months and
Days. eg
8548 converts into 23 Years, 4months and 24 days.
 
What about leap years? Do you take Y1, Y2, Y3 or Y4 as one?

With your number in A1, =ROUNDDOWN(A1/365.25,0) in B1;
=ROUNDDOWN((A1-(B1*365.25))/31,0) in C1 and
=ROUNDUP(A1-(B1*365.25)-(C1*31),0) in D1 will give your calculated answer of
23y, 4m and 24d.

However, while the 365.25 caters for leap years, 31 days in C1 and D1 are
incorrect, since months have varying numbers of days. If you replace the 31
with 30,4375, (in C1), and replace the formula in D1 with
=ROUNDDOWN(A1-(B1*365.25)-(C1*30.4375),0) you will get 23y, 4m and 25, iso 24
days.
 
Another play ..

With source numbers in A1 down.

Put in say, B1:
=IF(A1="","",TEXT(A1,"y")&" years, "&TEXT(A1,"m")&" months and
"&TEXT(A1,"d")&" days")
Copy B1 down
 
Agreed <g>.

8548 returns: 23 years, 5 months and 27 days

It was just an "approx." thought thrown in here.
I didn't want to get involved in the intricasies.
 
On Sun, 24 Dec 2006 00:13:00 -0800, K. Krishna Murthi <K. Krishna
I want to convert a number and display it as Number of Years, Months and
Days. eg
8548 converts into 23 Years, 4months and 24 days.
Click to expand...

Because both years and months have varying numbers of days, the "best" answer
to your question is not defined. You would have to first define exactly what
you mean by a year, and a month, in terms of days.

You could use the DATEDIF function. See
http://www.cpearson.com/excel/datedif.htm

Years: =DATEDIF(,A1,"y")
Months: =DATEDIF(,A1,"ym")
Days: =DATEDIF(,A1,"md")

So:

=DATEDIF(,A1,"y") & " Years, "&
DATEDIF(,A1,"ym")&" months and "&
DATEDIF(,A1,"md")&" days."


--ron
 
It looks like the only months when 8548 days added to a date will give a
difference of 23 years, 4 months and 24 daya are May, July and October.

Try using:

=DATEDIF(A1,A1+$E$1,"y")&" Years "&DATEDIF(A1,A1+$E$1,"ym")&" Months
"&DATEDIF(A1,A1+$E$1,"md")&" Days"

with the start date in A1 and 8548 in E1

( the $'s were because I dragged the formula down to a start date of 2015 to
check when you would get 24 days)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top