DATEDIF to show both positive and negative years, months and days

[Farknoes]

Hi,

I am trying to use a formula to show both positive and negative years, months and days between two date columns. I can get where I want showing a positive or negative number of months using
=IF(L2>M2,-DATEDIF(M2,L2,"M"),(DATEDIF(L2,M2,"M")))
Where column L is "Retail Sale Date" and column M is "Warranty Start Date", the problem I am having is some of the dates have been loaded incorrectly and I need to identify in years, months and days the difference.

I've used =DATEDIF(L2,M2,"Y")&" YEARS,"&DATEDIF(L2,M2,"YM")&" MONTHS,"&DATEDIF(L2,M2,"MD")&" DAYS"&IF(L2<M2,-DATEDIF(L2,M2,"Y")&" YEARS,"&-DATEDIF(L2,M2,"YM")&" MONTHS,"&-DATEDIF(L2,M2,"MD")&" DAYS") and it shows the positive values great, but I can't work out how to add to this to show the negative values

Can anybody help?

 

[steve the fish]

Changing what you have slightly i think you want:

=IFERROR(DATEDIF(L2,M2,"Y")&" YEARS,"&DATEDIF(L2,M2,"YM")&" MONTHS,"&DATEDIF(L2,M2,"MD")&" DAYS",-DATEDIF(M2,L2,"Y")&" YEARS,"&-DATEDIF(M2,L2,"YM")&" MONTHS,"&-DATEDIF(M2,L2,"MD")&" DAYS")

 

[Farknoes]

Perfect - that worked. thanks for your help!