Calculate Negative Dates

[erinm2022]

I'm using the DATEDIF function to show total years and months between two dates: =DATEDIF(I7,K7,"y")&" years, "&DATEDIF(I7,K7,"ym")&" months"

I then want to calculate the remaining years and months from the report date. I've done this by adding a column of report date and using the same function: =DATEDIF(L7,K7,"y")&" years, "&DATEDIF(L7,K7,"ym")&" months"

Problem is when it calculates negatively, I receive the #NUM! error. I do want to show the negative calculation to show how overdue it is.

Thanks in advance.

 

[Joe4]

Welcome to the Board!

Problem is when it calculates negatively, I receive the #NUM! error. I do want to show the negative calculation to show how overdue it is.

Flip around the order of columns K and L in your DATEDIF calculations to get a positive value.
Since you are building a string by concatenating these all together, you can add the negative (or whatever else you want) right in the formula, i.e.
="-" & DATEDIF(K7,L7,"y") & " years, " & DATEDIF(K7,L7,"ym") & " months"

 

[EFANYoutube]

Hi Erinm2022
Excel wont every display a negitive date, it will always be either ######## or #NUM! error
I would have an anchor year and work out the difference from there

=DATEDIF(1/1/1900,K7,"y")-DATEDIF(1/1/1900,I7,"y")&" years, "&DATEDIF(1/1/1900,K7,"ym")-DATEDIF(1/1/1900,I7,"ym")&" months"