change year date
 

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

change year date
 

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

--
Kind regards,

Niek Otten

"kevin" wrote in message
...
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

change year date
 

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" wrote:

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

--
Kind regards,

Niek Otten

"kevin" wrote in message
...
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

change year date
 

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.

--
Kind regards,

Niek Otten

"kevin" wrote in message
...
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" wrote:

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

--
Kind regards,

Niek Otten

"kevin" wrote in message
...
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

change year date
 

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.

"kevin" wrote:

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