pasted function

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

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

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

[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

[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

--
Don Guillett
SalesAid Software
[email protected]
"tng" <[email protected]> wrote in message
news:[email protected]...
>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]

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

[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