+ Reply to Thread
Results 1 to 14 of 14

Decimal to Years, Months & date conversion

  1. #1
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Decimal to Years, Months & date conversion

    Hi
    Did anyone know how to convert a decimal number to years,months & days.
    For ex 1.50=1years 6months , llly how to convert 1.56 , or any other decimal into y,m & days.

    I tried the below formula but its not working (Cell A1=1.5)
    INT(A1) for years
    INT(MOD(A1,365)/30) for months
    ROUND(MOD((MOD(A1,365)),30),0) for days


    Do i need to enable any add ins or options in excel. Plz send me the formula for this.

    Im trying this for the below problem to calculate the total experience till date , using the previous exp say(7yrs) and the date of joining.

    Prev Exp DOJ Total EXP
    7.00 05 June 2006
    1.90 22 March 2010
    6.40 14 May 2007
    4.00 14 September 2010

    I tried the below formula

    =CONCATENATE(SUM(DATEDIF(B2,TODAY(),"y"),INT(A2)),"years, ",SUM(DATEDIF(B2,TODAY(),"ym"),INT(MOD(A2,365)/30)),"Months, ",SUM(DATEDIF(B2,TODAY(),"md"),MOD((MOD(A2,365)),30)),"days.")


    Result: 11Years, 4Months, 6.2days (here year is coming correct but months and days are not right because of the decimal conversion.

    Appreciate anyone suggestion on this.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Decimal to Years, Months & date conversion

    There is an undocumented function called DATEDIF which you can use in this instance. Chip Pearson describes its use in detail here:

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

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Decimal to Years, Months & date conversion

    Hi..Thanks for ur reply. I am looking for decimal conversion to y,m, & days. can u help me on this?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Decimal to Years, Months & date conversion

    Suppose A1 contains 1.5, then you could have this formula in B1:

    =DATEDIF(100,100+A1*365.25,"y")&" years "&DATEDIF(100,100+A1*365.25,"ym")&" months "&DATEDIF(100,100+A1*365.25,"md")& " days"

    The first part, for example, could be:

    =DATEDIF(0,A1*365.25,"y")&" years "

    but then it would suffer from the bug in the 1900 date system, which treats the year 1900 as a leap year, so I've added 100 (days) onto both date parameters to help avoid this, and you can see that I've multiplied the decimal values (of years) in A1 by 365.25 - this is an approximation, though.

    Hope you can make use of this (I didn't really understand your second part).

    Pete

    EDIT: Actually, it gives better results if you change 100 to 60, like this:

    =DATEDIF(60,60+A1*365.25,"y")&" years "&DATEDIF(60,60+A1*365.25,"ym")&" months "&DATEDIF(60,60+A1*365.25,"md")& " days"

    Hope this helps.

    Pete
    Last edited by Pete_UK; 02-10-2012 at 02:19 PM.

  5. #5
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Decimal to Years, Months & date conversion

    date function.JPG

    Above is the excel problem, where column A contains Previous experience of employees ( in years(decimal) and column B shows the date of joining the company of employees. So here we need to calculate the experience with our company using (Datedif(B2,today(),"y").so it will be in years months and days. then we need to sum up the experience with our company and the previous experience(in coulmn A) to find out the total experience in Column C.Hope it will be clear now. could u plz help me on this? Thanks for your time and suggestion.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Decimal to Years, Months & date conversion

    Have you got a starting and end date for your number of years?

  7. #7
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Decimal to Years, Months & date conversion

    Starting date is the date of joining(in col B) and the end date is till now(ie today())..so i have used datedif function. then u need to sum up with the previous experience which is in decimal in column A...i guess u got it now...

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Decimal to Years, Months & date conversion

    Not sure why you want the result expressed in years months and days when your previous experience column is in decimal years (and the experience will increase continuously as you open the workbook on different days). You can get the number of days experience with the current company (in days) by subtracting the start date from TODAY(), i.e.:

    =TODAY()-B2

    and formatting the cell as General or Number with 0 dp. So if you divide this by 365.25 to get years then you can just add it onto the value in A2 to give the result in years, like this:

    =A2+ROUND((TODAY()-B2)/365.25,2)

    This will give you these results:

    12.69
    3.80
    11.15
    5.42

    when copied down column C.

    Hope this helps.

    Pete

    EDIT: If you really want the results expressed in years months and days then you can use the formula I posted in post #4, but it will need to look at C2 rather than A1, i.e. put this in D2:

    =DATEDIF(60,60+C2*365.25,"y")&" years "&DATEDIF(60,60+C2*365.25,"ym")&" months "&DATEDIF(60,60+C2*365.25,"md")& " days"

    and copy down.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 02-13-2012 at 08:58 AM.

  9. #9
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Decimal to Years, Months & date conversion

    Thanks Pete..I have the following formula and got the result in decimal and then tried converting that to years ,months & days. In that im not getting the days correctly



    =A2+ROUND((TODAY()-B2)/365.25,2) gives 12.69

    after that i used the below

    =IF(INT(ROUND(C2*12,0)/12),INT(ROUND(C2*12,0)/12)&"Years "," ")&IF(MOD(ROUND(C2*12,0),12),MOD(ROUND(C2*12,0),12)&"Months "," ")&ROUND((C2-INT(C2))*30,0)&"days "

    which gives 12 Years 8 Months 21days. .

    try this with a simple example

    say date of joining is 10th Jan 2012, So the experience with the company is 1month 3days.

    Consider Previous experience is 1yr.. so the total experience should be 1year 1month 3days. I am trying to get this result using the formula. But there is a problem in days calculation. hope u got it? Can u help on this?
    Last edited by m_skumar; 02-13-2012 at 09:28 AM.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Decimal to Years, Months & date conversion

    You are using 30 days for a month, which is an approximation (i.e. 360 days in a year), so you might get a better result if you used 30.4375 - there are 1461 days in 4 years, so divide that by 48 months. As I understand it, DATEDIF counts the number of (calendar) months between the start and end dates, and takes account of the number of days within each month. The formula that I gave you uses an effective start date of 1st March (using the 60 in the formula), and obviously every 4th year after that will give rise to a leap day. It is very imprecise working with months and days, which is why I questioned the reason you wanted to take this approach - I would just express the result as fractional years.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    02-10-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Decimal to Years, Months & date conversion

    Still im not getting the accurate result..just learning this out of my own interest. anyhow thanks so much for spending your valuable time for reply..

  12. #12
    Registered User
    Join Date
    12-26-2013
    Location
    Dubai
    MS-Off Ver
    Excel 365
    Posts
    21

    Re: Decimal to Years, Months & date conversion

    Man ... You are so great !

  13. #13
    Registered User
    Join Date
    11-14-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Decimal to Years, Months & date conversion

    You can use the INT() function to separate the decimal and get the years. Eg.
    Attachment 749260

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Decimal to Years, Months & date conversion

    Why are you responding to a thread which is over 9 years old ?

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1