converting data - re-formating data to "date" format

J

James

I have raw data that I export from a site for work and the date comes over as
Internationa ISO however it has no dashes in it to help format. Example it
looks like this... 20080302 just straight numbers. i need to convert this to
US date format.

Any ideas?
 
S

Sean Timmons

I would think Text to columns.

Ensure you have 2 columns available to the right of your date column.

Click Data - Text to Clumns...

Select Fixed Width and click between the 4th and 5th charatcers, then
between the 6th and 7th characters. Should break it out nicely. If you want
to combine back, you can enter a forumla to the right = month cell &"/"& day
cell &"/"& year cell.
 
K

Kevin B

Assuming that the data came across as text, the following formula will
convert the data to an Excel serial date number, changing A1 to the starting
cell of your value column/row:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

After copying the formula as far as needed, copy all the cell with a formula
and then do an EDIT/PASTE SPECIAL/VALUES to convert the formulas to their
respective result value.
 
Top