Convert 1.95 years to months in Excel

[shaleen7]

Hi All,

Trying to convert 1.95 to months and days in excel.

1.95 = 23 months.

Is there a formula I can use in excel?

Cheers

Thank You!

 

[TonyJollans]

The answer can only be approximate because months are not all the same. One way might be to calculate based on some arbitrary date. Based on 0 (nominally 31/12/1899), and ignoring leap years, this will do it (assuming your value of 1.95 is in cell A1):

[tt][blue]=DATEDIF(0,A1*365,"m") & " months, " & DATEDIF(0,A1*365,"md") & " days"[/blue][/tt]

Based on the current date:

[tt][blue]=DATEDIF(TODAY(),TODAY()+A1*365,"m") & " months, " & DATEDIF(TODAY(),TODAY()+A1*365,"md") & " days"[/blue][/tt]

Adding an extra day for every four years is a crude way to account for leap years but this will do that:

[tt][blue]=DATEDIF(TODAY(),TODAY()+A1*365+ROUNDDOWN(A1/4,0),"m") & " months, " & DATEDIF(TODAY(),TODAY()+A1*365+ROUNDDOWN(A1/4,0),"md") & " days"[/blue][/tt]

No doubt the Excel experts will have smarter answers


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[MakeItSo]

Quick answer:
=ROUND(A1*12,0)

Longer answer: what would you like 1,97 years to result in? 24 months? Do you wish to round or rather cut off thedecimals to give you the "complete months"?

ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[TonyJollans]

OK! Although the example was 1.95 = 23 months, I chose to go with the question, which asked for months and days.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[MakeItSo]

You are right, Tony! I totally overlooked the "days" mentioned, just saw the 23 as answer.
I did a quick calc and since the result is 23.4 months but the OP expected "23" I thought that's it.

@shaleen7: Can you please specify whether you also need days or weeks or parts of months?

ôKnowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.ö (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.