change year date

[kevin]

I have data that has dates of 4/25/200, 4/25/20,4/25/2020

How do I change the year to show 2006 without changing the month or day

 

[Niek Otten]

=DATE(2006,MONTH(A1),DAY(A1))

 

[kevin]

I apologize for multi-posting.

As for your formula it does not work
if you type 4/25/200 in one field and use your formula you get a #value! error

 

[Niek Otten]

Correct.

Excel is not able to work with dates before 1-1-1900. So 4/25/200 is not an
Excel date, 4/25/20 is, because Excel assumes 2020 then.
If you need this, you'll have to manipulate the "date" as a string (TEXT) to
extract month and day.

 

[Sloth]

you can use the find and replace function with dates. Just enter 200 in the
find and 2006 in the replace. Otherwise you will have to use text functions
because 4/25/200 is not a date as far as excel is concerned.

If you want a function something like this will work
=IF(ISTEXT(A1),DATE(2006,LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,1+FIND("/",A1))-FIND("/",A1)-1)),DATE(2006,MONTH(A1),DAY(A1)))

You need this longer version of the formula already posted because 4/25/200
is not a date as far as excel is concerned. It treats anything before 1900
and after 9999 as text.