Use VBA to subtract 3 years from a table of dates

[jase71ds]

I have tens of thousands of dates and I need to subtract 3 years from the dates.

The dates are in the American form of mm/dd/yyyy.

Let's say the range is, A1:Z100 (just to keep things simple)
Some of the cells are blank.
Some of the cells contain the text: "na" (no quotes)
But most of the cells have dates that need 3 years knocked off them. Thus 8-5-2018 would become 8-5-2015.

Any help is appreciated.
Thanks.
Jase.

 

[mole999]

add an extra column and display it there, if you run code to remove three years and then run it again, your data is damaged beyond use

 

[Jonmo1]

Try

Sub test()
For Each c In Range("A1:Z100").SpecialCells(xlCellTypeConstants, 1)
    If IsDate(c) Then c.Value = DateAdd("yyyy", -3, c.Value)
Next c
End Sub

 

[jase71ds]

Wow! Beautiful code. Works perfectly. Many thanks!!!!

Jase.

 

[Jonmo1]

You're welcome.