# converting a number to years months days

This is a discussion on converting a number to years months days within the Excel Questions forums, part of the Question Forums category; Hi can anyone tell me if possible if excel can convert a number such as 332 into years, months, and ...

1. ## converting a number to years months days

Hi can anyone tell me if possible if excel can convert a number such as 332 into years, months, and days? If so how? By the way I just bought this book (my mom did for me, im only 15 1/2) called excel 2003 formulas by john walkenbach. Im still new to excel so give me sometime...

One last question, is it possible, perhaps via macros or something to enter data and have the formula already written into the appropriate box and know the appropriate cell to retrieve the data. For example in cell A1 I have the persons birthday, in cell b1 I have a specific date in cell c1 I have the difference. When I enter the next persons birthday in a2 and a specific date in b2 can it automatically generate the differnce for me in c2 somehow? Thanks.

2. what are you working with - eg in the example, is 332 number of days old??

3. ## Re: converting a number to years months days

For differences between dates you can use DateDiff

=DATEDIF(Date1,Date2,Interval)

Where

Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.

Date1 must be less than (earlier) or equal to Date2. Otherwise, =DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a date, =DATEDIF will return a #VALUE! error.

Interval must be one of the following codes:

Code Meaning Description
"m" Months The number of complete months between Date1 and Date2.
"d Days The number of days between Date1 and Date2.
"y" Years The number of complete years between Date1 and Date2.
"ym" Months Excluding Year The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.
"yd" Days Excluding Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.
"md" Days Excluding Months And Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

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

FOR EXAMPLE :
In your situation put the formula in C1 as follows

=DATEDIF(A1,B1,"d")

If you want the entire Column C to use same formula just copy it from C1 to the entire column

4. ## Re: converting a number to years months days

I dont think DATEDIF will work since Im not calculating the number of days, months, or years between dates. Instead Im adding the total number of days from everyone's birth and finding the avg. For example Jane was born on 9-May-01 and based off 31-Oct-03 I know that she is 2 years 5 months and 22 days old, now taking that data I have converted it to just days which equals 1000 days. Now lets say I do that to about twenty different people finding the difference and then converting those to days. And I come to a total of 8653 days. I realize I take that number and divide it by the number of people. In this case I have twenty people which equals about 432.65. I now want to take that number and have it automatically convert into years, months, and days. That way I have the average age in years months and days of twenty people.

5. ## Re: converting a number to years months days

Not exactly tested, ubt a start:

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
 File Edit View Insert Options Tools Data Window Help About
 B2B3B4B5B6B8B9 =

A
B
C
D
1
DateDays*Old**
2
1/01/20011037**
3
1/01/20011037**
4
1/02/20011006**
5
1/03/2001978**
6
1/12/195019331**
7
****
8
Average:4678**
9
Age12*Years,*9*Months,*28*Days**
 Sheet1 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

the formula is:

=INT(B8/365)&" Years, "&INT(MOD(B8,365)/30)&" Months, "&ROUND(MOD((MOD(B8,365)),30),0)&" Days"

..which, even if along the right lines, is clearly an approximation given the assumptions about how long months & years are.

6. ## Re: converting a number to years months days

Good point on the 365 days and rounding, I didn't take that into account, that is an awesome formula by the way, can you point me in the direction of where you found it, or possible explain to me how you manage to come up with that formula on your own.

7. you want number of days expressed as years / months / days, so:

1) start off working out how many years:

INT(B8/365)

2) with that many years, how many months are left? - it'll be the integer portion of (number of days left after you've calculated the years) / (number of days in a month):

INT(MOD(B8,365)/30)

3) iterate the concept in (2) for the remaining days left once you've accounted for the years & months:

MOD((MOD(B8,365)),30)

& round to avoid decimals:

ROUND(MOD((MOD(B8,365)),30),0)

8. ## Re: converting a number to years months days

Originally Posted by PaddyD
Not exactly tested, ubt a start:

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
 File Edit View Insert Options Tools Data Window Help About
 B2B3B4B5B6B8B9 =

A
B
C
D
1
DateDays*Old**
2
1/01/20011037**
3
1/01/20011037**
4
1/02/20011006**
5
1/03/2001978**
6
1/12/195019331**
7
****
8
Average:4678**
9
Age12*Years,*9*Months,*28*Days**
 Sheet1 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

the formula is:

=INT(B8/365)&" Years, "&INT(MOD(B8,365)/30)&" Months, "&ROUND(MOD((MOD(B8,365)),30),0)&" Days"

..which, even if along the right lines, is clearly an approximation given the assumptions about how long months & years are.
Your logic is to die for Ta!!!!!!!

Pat Savage

9. ## Re: converting a number to years months days

Originally Posted by Pat Savage
Your logic is to die for Ta!!!!!!!

Pat Savage
Sometimes it's just staring you in the face!!!!!!!!

10. ## Re: converting a number to years months days

Originally Posted by Pat Savage
Sometimes it's just staring you in the face!!!!!!!!
Iv'e tweeked it to 365.25 and 30.33......over longer periods it's feckin close enough for
Jazz

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•