what are you working with - eg in the example, is 332 number of days old??
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 ...
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.
what are you working with - eg in the example, is 332 number of days old??
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
<MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>
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.
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
=
A B C D 1 Date Days*Old * * 2 1/01/2001 1037 * * 3 1/01/2001 1037 * * 4 1/02/2001 1006 * * 5 1/03/2001 978 * * 6 1/12/1950 19331 * * 7 * * * * 8 Average: 4678 * * 9 Age 12*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.
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.
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)
Like this thread? Share it with others