converting a number to years months days

childsremix

New Member
Joined
Oct 30, 2003
Messages
8
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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.
 
Upvote 0
Not exactly tested, ubt a start:
Book1
ABCD
1DateDaysOld
21/01/20011037
31/01/20011037
41/02/20011006
51/03/2001978
61/12/195019331
7
8Average:4678
9Age12Years,9Months,28Days
Sheet1



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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Not exactly tested, ubt a start:


******** ******************** ************************************************************************><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left>Microsoft Excel - Book1</TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: xl2000 : OS = Windows Windows 2000 </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#d4d0c8 colSpan=5><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">File Edit View Insert Options Tools Data Window Help About</TD><TD vAlign=center align=right><FORM name=formCb330421><INPUT value="Copy Formula" type=button name=btCb269281 *******='window.clipboardData.setData("Text",document.formFb352207.sltNb994220.value);'></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb352207><TD style="WIDTH: 60px" bgColor=white align=middle><SELECT onchange="document.formFb352207.txbFb438278.value = document.formFb352207.sltNb994220.value" name=sltNb994220><OPTION selected value==TODAY()-A2>B2<OPTION value==TODAY()-A3>B3<OPTION value==TODAY()-A4>B4<OPTION value==TODAY()-A5>B5<OPTION value==TODAY()-A6>B6<OPTION value==SUM(B2:B6)/COUNT(A2:A6)>B8<OPTION value='=INT(B8/365)&" Years, "&INT(MOD(B8,365)/30)&" Months, "&ROUND(MOD((MOD(B8,365)),30),0)&" Days"'>B9</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right>=</TD><TD bgColor=white align=left><INPUT value==TODAY()-A2 size=80 name=txbFb438278></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" width="2%" align=middle>

</TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>1</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid">Date</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid">Days*Old</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>2</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1/01/2001</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1037</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>3</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1/01/2001</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1037</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>4</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1/02/2001</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1006</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>5</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1/03/2001</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">978</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>6</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">1/12/1950</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">19331</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>7</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>8</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">Average:</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">4678</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>9</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">Age</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">12*Years,*9*Months,*28*Days</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #d4d0c8 0.5pt solid">*</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; BORDER-RIGHT: #000000 0.5pt solid">*</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left>Sheet1</TD><TD>*</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[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.</CENTER>


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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top