Converting days into Years and Months not working for some data

%3CLINGO-SUB%20id%3D%22lingo-sub-1956297%22%20slang%3D%22en-US%22%3EConverting%20days%20into%20Years%20and%20Months%20not%20working%20for%20some%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956297%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20formula%20below%20and%20its%20returning%20incorrect%20data%3C%2FP%3E%3CP%3E%3DINT(O83%2F365)%20%26amp%3B%20%22%20years%20and%20%22%20%26amp%3BMOD(O83%2C12)%26amp%3B%20%22%20months%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EO83%20%3D%20240%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20the%20result%20of%200%20Years%20and%200%20months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20same%20is%20for%20230%20returning%26nbsp%3B0%20Years%20and%26nbsp%3B2months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1956297%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956422%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20into%20Years%20and%20Months%20not%20working%20for%20some%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891177%22%20target%3D%22_blank%22%3E%40Tknerd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESuch%20formulas%20will%20only%20return%20an%20approximate%20result%2C%20since%20a%20year%20is%20not%20always%20365%20days%2C%20and%20since%20a%20month%20does%20not%20always%20have%20the%20same%20number%20of%20days.%20If%20you're%20willing%20to%20accept%20approximate%20results%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYears%3A%20%3DINT(O83%2F365.25)%3C%2FP%3E%0A%3CP%3EMonths%3A%26nbsp%3B%3DINT(MOD(O83%2C365.25)*12%2F365.25)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20a%20start%20date%20and%20end%20date%20in%2C%20say%2C%20M83%20and%20N83%2C%20more%20accurate%20formulas%20are%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYears%3A%20%3DDATEDIF(M83%2CN83%2C%22y%22)%3C%2FP%3E%0A%3CP%3EMonths%3A%20%3DDATEDIF(M83%2CN83%2C%22ym%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956450%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20into%20Years%20and%20Months%20not%20working%20for%20some%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956450%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891177%22%20target%3D%22_blank%22%3E%40Tknerd%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20super%20familiar%20with%20the%20formulas%20you're%20using%2C%20but%20I%20found%20one%20that%20seems%20to%20work%20fine%20for%20me%3A%3C%2FP%3E%3CP%3E%3DDATEDIF(0%2CA1%2C%22y%22)%20%26amp%3B%20%22%20Years%20%22%20%26amp%3B%20DATEDIF(0%2CA1%2C%22ym%22)%20%26amp%3B%20%22%20Months%20%22%20%26amp%3B%20DATEDIF(0%2CA1%2C%22md%22)%20%26amp%3B%20%22%20Days%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20replace%20A1%20with%20the%20cell%20containing%20your%20count%20of%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20short%20article%20explaining%20it%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excelhow.net%2Fhow-to-convert-days-to-years-months-days-in-excel.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelhow.net%2Fhow-to-convert-days-to-years-months-days-in-excel.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

3 Replies

@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")

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!

@Tknerd 

As variant

=YEAR(O83)-1900 & " years " & (MONTH(O83)-1) & " months"