excel formula to get years and months difference but in full 12 months

Question

I have this formula

    =IF(DATEDIF($C7; H$3; "y")=0; "";
    IF(DATEDIF($C7; H$3; "y")=1; "1 year";
        DATEDIF($C7; H$3; "y") & " years")) &
IF(DATEDIF($C7; H$3; "ym")=0; "12 months";
    IF(DATEDIF($C7; H$3; "ym")=12; "; 12 months";
        IF(DATEDIF($C7; H$3; "ym")=1; "; 1 month";
            "; " & DATEDIF($C7; H$3; "ym") & " months")
    )
)

is there a fix to say 17 years; 12 months after 17 years; 11 months instead of 18 years; 12 months?

Answer 1

I don't know why all delimiters in your formula are ; (e.g DATEDIF($C7; H$3; "y")). It should be , on Excel or GoogleSheet.

Following formula is tested on Excel 365.

=IF(DATEDIF($C7,H$3,"y")=0,"",IF(DATEDIF($C7,H$3,"y")=1,"1year",IF(DATEDIF($C7,H$3,"ym")=0,(DATEDIF($C7,H$3,"y")-1)&"years",DATEDIF($C7,H$3,"y")&"years")))&IF(DATEDIF($C7,H$3,"ym")=0,"; 12months",IF(DATEDIF($C7,H$3,"ym")=12,"; 12months",IF(DATEDIF($C7,H$3,"ym")=1,"; 1month","; "&DATEDIF($C7,H$3,"ym")&"months")))

LET formula is easier to understand and maintain.

=LET(
m,DATEDIF($C7,H$3,"ym"),
y,DATEDIF($C7,H$3,"y"),
mm,if(m=0,12,m),
yy,if(m=0,y-1,y),
mo,if(mm=1,"month","months"),
yr,if(yy=1,"year","years"),
CONCAT(yy,yr,"; ",mm,mo))

Answer 2

Another option:

=LET(yrs;DATEDIF($A2;B$1;"m")/12;
     sy;IF(INT(yrs)=1;"";"s");
     zerom;INT(12*(yrs-INT(yrs)))=0;
     sm;IF(ROUND(12*(yrs-INT(yrs));0)=1;"";"s");
     INT(yrs)-zerom & " year" & sy & " " & 
   ROUND(12*(yrs-INT(yrs));0)+(12*zerom) & " month" & sm)