Recognizing a date

[dzelnio]

"Wednesday, May 30, 2007 2:07:30 PM"

Shows up as text in column B of my worksheet. It is not defined as a
date and therefore sorts weird and cannot change format. This is a
result of the data collection method I cannot change without tons of
expense.

Ideas to convert this to a recognizable date so I can adjust it?

Dave

 

[Nick Hodge]

Copy an empty cell and highlight these 'text dates', select Edit>Paste
special...+add. Now format this 'number' as a date and all should be well

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Ragdyer]

Can you give more examples of the data?

Do you need the times?

Are there different years included?

TTC (Text To Columns), will work.
How to separate the data, and how many *times* you may need to perform a
separation within TTC depends on the answers to the above questions.

 

[dzelnio]

It showed up as #n/a. I pasted special and clicked "all" "add". No
go.
Also if it does work, can it be done as an automatic formula?
Dave

 

[kounoike]

I'm not sure this works in your case.
your date reside in column B and if all date start with weekday + ","
+....., put a formula like this in ,say, C2 and copy it down as you need
to, then sort data using column C.

=TRIM(REPLACE(B2,1,FIND(",",B2),""))+0

keizi

 

[dzelnio]

Here's an example of the date/time:
Monday, June 4, 2007 7:39:38 AM

Eventually different years will be included.

I just want Excel to recognize this as a date so I can sort.

: ) Dave

 

[dzelnio]

Here's the date:
Monday, June 4, 2007 7:39:38 AM
Here's what your formula called it:
39237.31919
Hmm.
Dave

 

[dzelnio]

Forget this reply. Great formula. I just reformatted the numbers to
a date and all is well!
Dave

 

[kounoike]

I'm glad to hear it worked.

keizi

Forget this reply. Great formula. I just reformatted the numbers to
a date and all is well!
Dave