# Converting decimals to years/months with negatives

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Rick Rothstein

##### MrExcel MVP
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",""))

Last edited:

#### Tetra201

##### MrExcel MVP

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

##### MrExcel MVP

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

##### MrExcel MVP
@ 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.

##### New Member
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!

##### New Member
Thank you very much!

Out of interest, what is the TRUNC function?