Selection.Replace Problem

D

Dave

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
 
D

David Biddulph

Look at your Windows Regional Options (through Control Panel) to sort out
the DMY/MDY conflict.
 
D

David Biddulph

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?
 
D

Dave

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
 
D

Dave Peterson

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
 
D

David Biddulph

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)?
 
D

David Biddulph

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.
 
D

Dave Peterson

And my post showed how they could be converted correctly without looking at the
regional settings.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
is it possible to sort by approximate date? 2
Peculiar date format behavior when replacing parts of date 1
date format problem using Macro 1
3 cells into 1 date? 3
Date glitch? First 12 days of month format differently than the r 3
Returning the month (mmm) from cell in date format dd/mm/yyyy 3
Formula to fill cells based on Date of Arrival and Date of Departure 1

Top