convert 3months and 15 days into 3.5

[caz46]

Hi I have created a formula to calculate the number of years, months and days between two dates, as follows:




"=IF(DATEDIF(A9,B9,""y"")=0,"""",DATEDIF(A9,B9 ,""y "")&"" years "")&IF(DATEDIF(A9,B9,""ym"")=0,"""",
DATEDIF(A9,B9,""ym"")&"" months "")&DATEDIF(A9,B9,""md"")&"" days"""

This works perfectly and returns a value of 1 year, 3 months and 15 days for example.

However what I would like it to return is 3.5 months or if the number of days is below 15 then round the number down to 3months.

Could you please help with this?

Many Thanks

[Catalin.B]

Here is an idea to play with:

=IF(DATEDIF(I8;G8;"m")=0;"";DATEDIF(I8;G8;"m"))+IF(DATEDIF(I8;G8;"md")<15;0;1)&" months"

It will add 0 months to the initial month calculation if the days number are <15, and add 1 month if days are>=15
instead of the above formula, you can use:
=IF(DATEDIF(I8;G8;"m")=0;"";DATEDIF(I8;G8;"m"))+CE ILING(DATEDIF(I8;G8;"md")/30;0,5)&" months"
to get decimal results...and so on

Change ; with , to match your local settings

[caz46]

That's great and works perfectly. Thanks very much.

[Catalin.B]

Great... Than you can mark your thread as solved, for users with same problems...

[caz46]

Originally Posted by Catalin.B

Great... Than you can mark your thread as solved, for users with same problems...

I can't see where I can do that?

[Catalin.B]

you have Thread Tools option in your thread, on the blue line above, select from drop down menu: Mark this thread as solved