Converting decimals to years/months with negatives

Palacemad

New Member
Joined
May 19, 2019
Messages
32
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!
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
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
Joined
Oct 14, 2016
Messages
3,542
@ 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
@ 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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
@ 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

New Member
Joined
May 19, 2019
Messages
32
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

New Member
Joined
May 19, 2019
Messages
32
Thank you very much!

Out of interest, what is the TRUNC function?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,043
Messages
5,466,198
Members
406,472
Latest member
QuarkJaguar

This Week's Hot Topics

Top