MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I subtract a number of years from a date?

Posted by Tommy Lee on January 25, 2000 11:21 AM

Is there a way to subtract a number of years from a date in cell "A1" (mm/dd/yyyy) and display the resulting date in cell "B1" in (mm/dd/yyyy)? The desired result should look like 10/10/1999 - 3 years = 10/10/1996. Some kind of "year()" "date()" function formula? Any help would be appreciated

Posted by Chris on January 25, 2000 12:03 PM

Here's one way to do it:

=DATEVALUE(MONTH(E9) & "/" & DAY(E9) & "/" & YEAR(E9)-3)

Say hi to Pam for me.


Posted by Tim on January 25, 2000 1:13 PM

try this in B1 -

=DATE(MONTH(A1), DAY(A1), YEAR(A1) - 3)

Posted by david on May 12, 2000 9:05 AM

I tried both of these and could not get it to work for adding something like 12 months i took the number of munths /12 and replaced that with the 3 always get an error any sug.