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.
--
David Biddulph

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

Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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

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


Dave wrote:

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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







--

Dave Peterson

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)?
--
David Biddulph

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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

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.
--
David Biddulph

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


Dave wrote:

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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







--

Dave Peterson

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

David Biddulph wrote:

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.
--
David Biddulph

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


Dave wrote:

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Dave" wrote in message
...
Thanks David but that doesnt seem to be the problem.

"David Biddulph" wrote:

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

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







--

Dave Peterson

--

Dave Peterson