Recognizing a date

  • Thread starter Thread starter dzelnio
  • Start date Start date
D

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
 
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.
 
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
 
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
 
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
 
Here's the date:
Monday, June 4, 2007 7:39:38 AM
Here's what your formula called it:
39237.31919
Hmm.
Dave
 
Forget this reply. Great formula. I just reformatted the numbers to
a date and all is well!
Dave
 
Back
Top