Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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. #1
    New Member
    Join Date
    Oct 2003
    Posts
    8

    Default 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. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

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

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default 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

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

  4. #4
    New Member
    Join Date
    Oct 2003
    Posts
    8

    Default 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. #5
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default 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
    =

    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. #6
    New Member
    Join Date
    Oct 2003
    Posts
    8

    Default 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. #7
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    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. #8
    New Member
    Join Date
    Apr 2011
    Location
    Scotland
    Posts
    3

    Default Re: converting a number to years months days

    Quote Originally Posted by PaddyD View Post
    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
    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. #9
    New Member
    Join Date
    Apr 2011
    Location
    Scotland
    Posts
    3

    Default Re: converting a number to years months days

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

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

  10. #10
    New Member
    Join Date
    Apr 2011
    Location
    Scotland
    Posts
    3

    Default Re: converting a number to years months days

    Quote Originally Posted by Pat Savage View Post
    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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com