Formating Dates

[Guest]

Hi,
I have a list of dates in this format;

20071105
yyyymmdd

I would like to have these displayed in excel in a more easy-to-read format,
such as dd/mm/yy. If i simply try to format these cells as a date in excel
it predictably treats them as negative dates and displays them as ########.
Is there a way I can get excel to format these cells correctly, without
having to manually type in a huge list of dates?

Thanks,
Tom

 

[Guest]

A1 = 20071105
B1 = =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Format B1 as Date mm/dd/yy

HTH

Jim May

 

[David Biddulph]

Data/ Text to Columns/ Next/ Next/ Column date format: Date: YMD/ Finish
Format your cell to suit.

 

[Guest]

Thanks Jim, This worked brilliantly!

A1 = 20071105
B1 = =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Format B1 as Date mm/dd/yy

HTH

Jim May