Converting decimals to years/months with negatives

[Palacemad]

Hi, I have some data that is represented in decimals. I want this data, in a different cell to be represented as months. For positive values it works fine, however for negatives it does not.

My initial formula was as follows:

=IF(AJ5="","",(IF(INT(AJ5),INT(AJ5)&" Years ","")&IF(MOD(AJ5,1),ROUND(12*MOD(AJ5,1),0)&" Months","")))

However, for a decimal value (in cell AJ5) of -0.67, it is returning - 1 years 4 Months, instead of -1 years 8 months.

I recognise that the formula is calculating the months the wrong way round.

Has anyone got any suggestions to correct this??

Thank you!

 

[Rick Rothstein]

I am not sure why you are saying that -0.67 should be more than a year's worth of months... I think it should just be -0 years 8 months. If you agree, give this formula a try...

=IF(AJ5="","",SUBSTITUTE(TEXT(AJ5,"0 ""Years"" 0/12 ""Months"""),"/12",""))

 

[Tetra201]

@ Palacemad:

Here is one of the ways of fixing your formula:

=IF(AJ5="","",IF(AJ5<0,"-","")&IF(TRUNC(AJ5),INT(ABS(AJ5))&" Years ","")&IF(MOD(AJ5,1),ROUND(12*MOD(ABS(AJ5),1),0)&" Months",""))

 

[Rick Rothstein]

@ Palacemad:

Here is one of the ways of fixing your formula:

=IF(AJ5="","",IF(AJ5<0,"-","")&IF(TRUNC(AJ5),INT(ABS(AJ5))&" Years ","")&IF(MOD(AJ5,1),ROUND(12*MOD(ABS(AJ5),1),0)&" Months",""))

While I like the formula I posted in Message #2 better, I think this alternative based on the approach you used above would also work...

=IF(AJ5="","",IF(AJ5<0,"-","")&INT(ABS(AJ5))&" Years "&ROUND(12*MOD(ABS(AJ5),1),0)&" Months")

 

[Tetra201]

@ Rick:

I also like the formula from Post #2 better. By the way, it could be tweaked further to prevent showing 0 years -- to mimic the OP's formula output:

=IF(AJ5="","",SUBSTITUTE(TEXT(AJ5,"# ""Years"" 0/12 ""Months"""),"/12",""))

Anyways, with Post #3 , I just wanted to introduce the OP to the TRUNC function.

 

[Palacemad]

Thank you for the replies, I will give those a try.

Rick, sorry yes, I don’t know why I said -1 year 8 months rather than -8 months. I think that’s the effect of sitting for over an hour trying to figure out what was going wrong!

 

[Palacemad]

Thank you very much!

Out of interest, what is the TRUNC function?

 

[MARK858]

@Palacemad, please see the link below

https://corporatefinanceinstitute.com/resources/excel/functions/trunc-function/

 

[Rick Rothstein]

Out of interest, what is the TRUNC function?

Read the entire article to get the clearest picture of what this function does and how it differs from the INT function...

https://exceljet.net/excel-functions/excel-trunc-function