Searching and Replacing Dates

[Closed]
Report
Posts
1
Registration date
Tuesday August 9, 2016
Status
Member
Last seen
August 9, 2016
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
I need to search and replace dates. For example, in one row (representing one employee), all dates prior to 8/9/16 and replace those dates with 8/9/16. I've tried search and replace, but that isn't quite it. It doesn't recognize the "less than" symbol. Is there an "if ... then... " statement and how would I use it?

Time sensitive problem.

Thank you!

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi FranBacAero,

Assuming the dates are located in one column, try this (after changing the 3 A's on the 2nd code line to the column letter referring to your date column):
Sub RunMe()
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value <> vbNullString And cell.Value < Now Then
        cell.Value = Format(Now, "m-d-yy")
    End If
Next cell
End Sub


In case you don't know:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.



Best regards,
Trowa