Date 00/00/00 change year only

Copper Contributor

Have a column of dates with the wrong year:

??/??/22 need to change the year to ??/??/21

I've formatted the column to Text.

Selecting the column to limit Find

How do I Find the /22 year instances without also selecting days that also happen to be 00/22/22


Seems wildcard characters should work, but no?

4 Replies


You can search for */*/22

@Hans Vogelaar works well, thanks for that

Can you help me with the replace formula?

Replace only the year without changing month/day/year?

best response confirmed by Sergei Baklan (MVP)


Let's say you have dates in D2 and down.

In E2:

=DATE(2023, MONTH(D2), DAY(D2))

Fill down.

Most helpful-Thanks again