Forum Discussion

Tknerd's avatar
Tknerd
Copper Contributor
Dec 04, 2020

Converting days into Years and Months not working for some data

Hi 

 

I have the formula below and its returning incorrect data

=INT(O83/365) & " years and " &MOD(O83,12)& " months"

 

O83 = 240 days

 

I am getting the result of 0 Years and 0 months.

 

The same is for 230 returning 0 Years and 2months.

 

Please help.

excel

3 Replies

  • sally365's avatar
    sally365
    Brass Contributor
    Dec 04, 2020

    Hey Tknerd,

     

    I'm not super familiar with the formulas you're using, but I found one that seems to work fine for me:

    =DATEDIF(0,A1,"y") & " Years " & DATEDIF(0,A1,"ym") & " Months " & DATEDIF(0,A1,"md") & " Days "

     

    Just replace A1 with the cell containing your count of days.

     

    Here's a short article explaining it: https://www.excelhow.net/how-to-convert-days-to-years-months-days-in-excel.html

     

    I hope this helps!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 04, 2020

    Tknerd 

    Such formulas will only return an approximate result, since a year is not always 365 days, and since a month does not always have the same number of days. If you're willing to accept approximate results:

     

    Years: =INT(O83/365.25)

    Months: =INT(MOD(O83,365.25)*12/365.25) or =INT(MOD(O83*12/365.25,12))

     

    If you have a start date and end date in, say, M83 and N83, more accurate formulas are

     

    Years: =DATEDIF(M83,N83,"y")

    Months: =DATEDIF(M83,N83,"ym")

Resources