Date Formatting Help

[steeld95]

I am trying to change the format in a column to a specified date format (mm/dd/yyyy). I am importing my data from an xml. When the data comes into excel, it comes in the following format yyyymmdd. It is formated as general text. I am highlighting the row and hitting format cells. I then choose the date category and try to format it to 3/14/2011 format. The cell then changes to #########.

Is ther a way I can convert the date format I am bringing in from the xml to what I need it to look like?

Thanks for the help!

 

[doubledaffy]

IF it's coming in as general text, try converting it to a value using VALUE(A1).

There might be more that has to be done to get it to the right format though.

 

[Rick Rothstein]

Select your column of numbers, press ALT+DE to bring up the Text To Coluumns dialog box, click Next twice, select the Date option button and choose YMD from it drop down box, then click Finish. You now have real dates which you can Cell Format to display anyway you want it to.

 

[barry houdini]

If you are using the wizard to import then you should be able to set the column format to YMD then it'll convert on import - alternatively you can convert after import in a similar manner

Select column of data then use

Data > Text to columns > Next > Next > under "column data format" choose "date" and the "YMD" from the dropdown > Finish

That should convert to true dates - format as required

 

[steeld95]

Thanks so much! Using the 'Text to Columns' feature worked exactly how I needed it show. I really appreciate it!