Replace

[sythong]

Hello again to my friend out there.

I have cells formatted as dd-mm-yy but when I
tried replacing the month by typing in text it does not
work.

Eg date is Jun 2006 and I want to replace it to May it
says no match (Replace June with May). This is driving
me nuts because when I use a number to replace all
days month or year containg 6 will be replace with 5
when I only want to replaced the month only

26 Jun 2006 to changed to 26 May 2006

I get 25-May-2005

Any thoughts?

Thanks again.


Thong

 

[Scott Huish]

That's because Excel doesn't store dates as text. Excel stores dates starting with 1, which (in the 1900 date system) is January 1, 1900, 2 is January 2, 1900, and so on. So today July 20, 2007 is 32983.

If you have a date cell of 6/26/06 formatted as dd mmm yyyy, this is just changing the format of the cell, not what the cell actually contains
Go up to the formula bar and change the month in the date.

or you could use a formula:
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))