How to change "text" date to numeric date

[wec43wec]

When data from a supplied data base is moved to excel, the dates in cell (A1 through A100)is TEXT and left justified in each cell.(ie May, 25, 2009 is listed as May-25-2009). I believe the data is from a CSV file. When I try to save the file; the Save As (pop up window) shows the Save As Type as “Text(Tab delimited)”

Is there an option in Excel (Non VBA) that will change the text date to a numeric / number? Please note the X (times) 1 option does not work nor any of the format functions I've used.

Please advise


Thanks

 

[SkipVought]

hi,

When data from a supplied data base is moved to excel, the dates in cell (A1 through A100)is TEXT ...

FIRST, string the provider up by his thumbs!!! I would be livid! What an idiotic thing to send to Excel!

Ok, my rant is over. Make a list of 3-character months. If you start with Jan, and copy/drag for 12 cells, Excel generates the months of the year. Name your list MonList
[tt]
=DATE(RIGHT(A1,4),MATCH(LEFT(A1,3),MonList,0),MID(A1,5,2))
[/tt]
where A1 is your "yuk date"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Text to Cloumns (under Data menu) will work as well.

Step 1 choose Delimited
Step 2 skip
Step 3 choose Date and under the drop down MDY



I don't particularly care about apathy

 

[combo]

There is more freedom in defining input columns format after the change of the file extension from 'csv' to 'txt'. Frequently it's helpful when dealing with non-standard storing of dates.

combo

 

[SkipVought]

Geoff's method is much MUCH better than mine...

...AND, it contains skip in Step 2, so how CAN you go wrong???!!! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

Skip - too funny!



GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]