Convert a string to a date?
 

I have a series of entries in col A like this:

Tuesday 7th September 2004
Tuesday 3rd April 2007
etc.
FWIW, all happen to start 'Tuesday' in the present case, although a
fully general solution would be ideal in case I come across
non-Tuesday variations in future.

Is there a simple way to get these into a proper date format please?
I'd be happy with various types, my aim being to abbreviate them. So
for example

Tue 7 Sep 2004
7/9/07
or even
Tuesday 7th Sep 2004

would be OK. Obviously, once I have them in Date format, I can
experiment with the alternatives.

An alternative I suppose would be to do a global Replace. But that
would need repetitions, to change 'January' to 'Jan', February' to
'Feb', etc.

--
Terry, East Grinstead, UK

Convert a string to a date?
 

Text to Columns, format each column then concatenate.

"Terry Pinnell" wrote:

I have a series of entries in col A like this:

Tuesday 7th September 2004
Tuesday 3rd April 2007
etc.
FWIW, all happen to start 'Tuesday' in the present case, although a
fully general solution would be ideal in case I come across
non-Tuesday variations in future.

Is there a simple way to get these into a proper date format please?
I'd be happy with various types, my aim being to abbreviate them. So
for example

Tue 7 Sep 2004
7/9/07
or even
Tuesday 7th Sep 2004

would be OK. Obviously, once I have them in Date format, I can
experiment with the alternatives.

An alternative I suppose would be to do a global Replace. But that
would need repetitions, to change 'January' to 'Jan', February' to
'Feb', etc.

--
Terry, East Grinstead, UK

Convert a string to a date?
 

Hi Terry,

For a 'date' in A1, try:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(MID(A1,FIND(" ",A1)+1,LEN(A1)),"st",""),"nd",""),"rd",""),"th"," "))
and format the result as a date.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Terry Pinnell" wrote in message ...
I have a series of entries in col A like this:

Tuesday 7th September 2004
Tuesday 3rd April 2007
etc.
FWIW, all happen to start 'Tuesday' in the present case, although a
fully general solution would be ideal in case I come across
non-Tuesday variations in future.

Is there a simple way to get these into a proper date format please?
I'd be happy with various types, my aim being to abbreviate them. So
for example

Tue 7 Sep 2004
7/9/07
or even
Tuesday 7th Sep 2004

would be OK. Obviously, once I have them in Date format, I can
experiment with the alternatives.

An alternative I suppose would be to do a global Replace. But that
would need repetitions, to change 'January' to 'Jan', February' to
'Feb', etc.

--
Terry, East Grinstead, UK

Convert a string to a date?
 

"macropod" wrote:

Hi Terry,

For a 'date' in A1, try:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(MID(A1,FIND(" ",A1)+1,LEN(A1)),"st",""),"nd",""),"rd",""),"th"," "))
and format the result as a date.

Cheers

Thanks both, I'll try those suggestions.

--
Terry, East Grinstead, UK