Converting numbers to date format

Hi,

I have a few columns E,X and Z with the dates in the form of 20120426 and I need to convert it to 04/26/2012 format using VBA in Excel 2003.

Any help is appreciated.

Thanks,

David

David,

Give this a try:

Sub tgr()
    
    Dim rngArea As Range
    Dim lCalc As XlCalculation
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    On Error GoTo CleanExit
    
    For Each rngArea In Range("E:E,X:X,Z:Z").SpecialCells(xlCellTypeConstants).Areas
        With rngArea
            If .NumberFormat <> "mm/dd/yyyy" Then
                .Value = Evaluate("If(IsNumber(" & .Address & "),--Text(" & .Address & ",""0000\/00\/00"")," & .Address & ")")
                .NumberFormat = "mm/dd/yyyy"
            End If
        End With
    Next rngArea
    
CleanExit:
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    If Err.Number <> 0 Then
        MsgBox Err.Description, , "Error: " & Err.Number
        Err.Clear
    End If
    
    Set rngArea = Nothing
    
End Sub

djfscouse,

From your original post:

Originally Posted by djfscouse

I have a few columns E,X and Z with the dates in

However, your provided example file has the dates in columns E, Y, and AA. The macro is easy enough to adjust though:

'Change this line
For Each rngArea In Range("E:E,X:X,Z:Z").SpecialCells(xlCellTypeConstants).Areas

'To be this instead
For Each rngArea In Range("E:E,Y:Y,AA:AA").SpecialCells(xlCellTypeConstants).Areas