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.

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.

Again, if all the dates are in the same format then you could also use
this,

=VALUE(TEXT(SUBSTITUTE(A14,",","/"),"yyyy/mm/dd"))


Copy down your list of text dates. You can change the format as you
want by changing the "yyyy/mm/dd" to another date format.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513838

one way
Sub makedate()
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
x = Left(c, 4)
y = InStr(1, c, ",")
z = InStr(y + 1, c, ",")
c.Value = DateSerial(x, Mid(c, y + 1, z - y), Right(c, Len(c) - z))
Next
End Sub

--
Don Guillett
SalesAid Software

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

The 32nd of December????

Maybe you could just select that single column and do data|text to columns.

Choose fixed width and don't draw any lines.
Choose ymd as the format
and finish up.

Format it the way you like.

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.

--

Dave Peterson

Kevin B's solution resolved my conversion, Thanks.

I still wonder why the cell showed =date(2005,12,32) and would only
calculate when I selected the cell and hit enter.

Folks, Thanks for your feedback.

Tom