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

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

HTH

Jim May


"Tom Roberts" wrote:

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

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

"Tom Roberts" wrote in message
...
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

Thanks Jim, This worked brilliantly!

"JMay" wrote:

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

HTH

Jim May


"Tom Roberts" wrote:

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