if all the dates are yyyy,mm,dd you can use the following date formula in a
single cell:

=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,6,2)),VALUE(R IGHT(A1,2)))
DATE(Year, Month, DAY)
The LEFT function extracts the first 4 characters from the string and VALUE
converts them to a value giving you the year.

MID extracts 2 characters starting at position 6 and Value converts it to a
number for the month.

RIGHT extracts the last 2 characters on the right, and VALUE converts it to
a number for the day.

--
Kevin Backmann


"tng" wrote:

I have a BIG list of text dates I need converted to date format.
You cannot use =date(A1)

A b c
2005,12,32 ="date("&A1&")" =date(2005,12,32)

I enter the formula into column B and it displays a date formula
I copy B and paste special - values into C and it looks as above.
If I select C and hit enter, the formula caclulates and I get 12/32/2005
The problem is I have 7000 more cells to calculate. I have tried F9 and also
closing the file and reopening and it won't calculate the remaining cell.

Any help would be appreciated.