Dates shown as text - how to convert

[Colleyville Alan]

When I use the trim function on some dates that have been input as text, the
leading spaces are gone. But Excel still treats the dates as though they
were text

However, if I hit the F2 key to enter the edit mode and then press Enter, it
converts the information to dates. How do I convert thess quasi-text cells
without manually editing each one? Is there a worksheet function that will
do this?
Thanks

 

[Anders S]

Try this:

- copy any empty cell
- select the problem cells
- do Edit>Paste Special, click Add, then OK
- do Format>Cells>Date

HTH
Anders Silvén

 

[Don Guillett]

try this

Sub ConvertThem() 'Harald Staff
Dim C As Range
For Each C In Intersect(Selection, _
ActiveSheet.UsedRange)
If Not C.HasFormula Then
If IsNumeric(C.Value) Then
C.Value = C.Value * 1
End If
End If
Next
End Sub

 

[Colleyville Alan]

try this

Sub ConvertThem() 'Harald Staff
Dim C As Range
For Each C In Intersect(Selection, _
ActiveSheet.UsedRange)
If Not C.HasFormula Then
If IsNumeric(C.Value) Then
C.Value = C.Value * 1
End If
End If
Next
End Sub

This code works fine for numeric values that Excel thinks are strings, but
it does not seem to work on dates.

 

[Colleyville Alan]

Try this:

- copy any empty cell
- select the problem cells
- do Edit>Paste Special, click Add, then OK
- do Format>Cells>Date

Thanks - works great.