Selection.Replace Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone.

I'm having problems with the Selection.Replace function.

I have a column with D.O.B's in the format DD.MM.YYYY e.g. 14.07.1987

I would like to replace all .'s with /'s i.e. to DD/MM/YYYY e.g. 14/07/1987

I have done this using a macro. All dates with days over 12 come over fine
e.g. 14th July. But all dates with days under 12 e.g. 12th July, 11th July do
not format properly.

24.07.1987 becomes 24/07/1987

11.07.1987 becomes 07/11/1987

i.e. the .'s have been replaced, but now the dates are wrong. They seem to
have become MM/DD/YYYY



Can anyone tell me how to correct this or tell me a macro from scratch.

Thanks!
Dave
 
Look at your Windows Regional Options (through Control Panel) to sort out
the DMY/MDY conflict.
 
Are you saying that your Windows Regional Options call for DMY (i.e. short
date in the format 20/09/2007), but Excel is still reading an input of
11.07.1987 as if it were 7th November?
 
If I have a list of dates as follows:


01.09.2007
04.11.2002
24.08.1999
31.07.2009
01.03.2002

and then I try to convert them to have /'s in Excel, they appear as:

09/01/2007
11/04/2002
24/08/1999
31/07/2009
03/01/2002

Does this make sense?

Thanks
Dave
 
My bet is these aren't dates--they're just text that are masquerading as dates.

Select your range (single column at a time)
data|text to columns
fixed width (remove any lines excel guessed and don't add any yourself)
Choose date and dmy
Excel will convert these to real dates.
Format them the way you want via format|cells|number tab
 
That is what I would expect it to do if you have your Windows Regional
Settings calling for MDY, rather than DMY.

Have you checked your Windows Regional Settings? Do they call up a short
date of 20/09/2007 (i.e. DMY) or 09/20/2007 (i.e. MDY)?
 
If it has changed 01.09.2007 to 09/01/2007, it has presumably recognised
them as dates, but misinterpreted them for the reason I outlined.
 
And my post showed how they could be converted correctly without looking at the
regional settings.
 
Back
Top