How to change the format of a field in an Excel TABLE?

[Rhino_Dance]

I have data in an Excel 'table' which was returned from MS QUERY. (I'm using the word 'Table' formally here). One field has entries that look like dates as in '2012-04-01' but the field is a text field and not a date field, and I need to change it to a date field to make it useful to me. The way I can tell it's not a date field is that I get a 'TRUE' to an ISTEXT function on it, as well as the fact that the results I get when I run an IF statement against the field confirm that none of the dates are passing tests that they should.

My Question: How can I change this field to a date field? I've tried highlighting the whole column and using the 'Format Cells' command to change it to a date format but it still remains a text field. Also, I don't want to add any companion fields as I 'm trying to automate a complex spreadsheet and write formulas directly against the query result. Thanks.

 

[Andrew Poulsom]

Select the dates and choose Text To Columns on the Data tab. At Step 3 of the Wizard choose YMD against Date and click Finish.

 

[Rhino_Dance]

Thanks Andrew. It's good to know that's an option. In the end, the source of the problem turns out to be that the underlying Sql Server db had the field stored as text and not as a date (even though it looks exactly like a date). So I modified the SQL code in the MS Query to convert the field to a date using CONVERT(datetime, Start_Date, 101) which turns a text entry like '2012-04-01' into an actual date field like '4/1/12'.