Format Dates in Column A as MM/DD/YYYY

User request

Loop through column A and format dates as MM/DD/YYYY

Option Explicit

Sub FormatDatesInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cell As Range
    Dim cellValue As Variant
    
    ' Set worksheet explicitly (change "Sheet1" to your sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each cell in column A from row 1 to lastRow
    For i = 1 To lastRow
        Set cell = ws.Cells(i, "A")
        cellValue = cell.Value
        
        ' Skip if cell is empty
        If cellValue <> "" Then
            ' Check if the value is a date or can be converted to a date
            If IsDate(cellValue) Then
                ' Format as MM/DD/YYYY
                cell.NumberFormat = "MM/DD/YYYY"
                cell.Value = CDate(cellValue)
            Else
                ' Optional: message for non-date values (can be removed)
                ' MsgBox "Cell A" & i & " is not a valid date."
            End If
        End If
    Next i
    
    MsgBox "Date formatting completed."
End Sub

What the macro does

• The macro loops through each cell in column A from row 1 to the last used row.
• It checks if each cell contains a value that can be interpreted as a date using IsDate.
• If valid, it sets the number format to MM/DD/YYYY and converts the value to a date type.
• Empty cells and non-date values are skipped (optional message can be enabled).
• The worksheet is explicitly referenced to avoid ambiguity.

How to use it

1. Press Alt+F11 to open the VBA editor.
2. Insert a new module (Insert > Module).
3. Copy and paste the provided code into the module.
4. Modify the sheet name in the line 'Set ws = ThisWorkbook.Sheets("Sheet1")' to match your worksheet.
5. Run the macro (F5) while the workbook is active.
6. Check column A; dates should now display as MM/DD/YYYY.