pasted function

T

tng

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.
 
K

Kevin B

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(RIGHT(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.
 
S

SteveG

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
 
D

Don Guillett

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
 
D

Dave Peterson

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.
 
T

tng

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
 
Top