Replace

sythong

Active Member
Joined
Jun 26, 2004
Messages
324
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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))
 

Forum statistics

Threads
1,181,053
Messages
5,927,852
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top