Dates shown as text - how to convert

  • Thread starter Thread starter Colleyville Alan
  • Start date Start date
C

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
 
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
 
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
 
Don Guillett said:
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
Click to expand...

This code works fine for numeric values that Excel thinks are strings, but
it does not seem to work on dates.
 
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.
 
Back
Top