Date Format Normalizer: Standardize Every Date Across Your Entire Workbook

TL;DR: You open a consolidation workbook with dates from three systems — the ERP exported them as text (2026-06-15), the bank feed uses mm/dd/yyyy, and the client schedule uses 15-Jun-2026. No pivot table group by month, no SUMIFS by quarter. This macro catalogs every date-like value across every sheet into a report, then standardizes them all to one format with a single click.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with dates in mismatched formats — text strings, real dates with different number formats, or both
• The macro creates a Date-Format-Report sheet at the end of the workbook; your original data is never deleted (the report is informational, the standardization only changes number formats and converts text dates)

Limitations:

• Ambiguous dates are flagged, not auto-converted. If a text cell reads "03/04/2026", the macro can’t know whether you meant March 4 or April 3. These show as “Text Date” in the report with the original text intact — review and fix them manually.
• Only standardizes cells the macro identifies as date-like. Numbers formatted as numbers (e.g., serial number 46000 displayed as 46000) are skipped even if they happen to be Excel date serials. The macro requires a date-like number format or date-like text pattern.
• Does not auto-detect the “correct” format. You choose the target format via an InputBox. The macro standardizes everything to your choice — it doesn’t try to guess which format is right.

#The Macro

Option Explicit

Sub DateFormatNormalizer()
    ' ── Date Format Normalizer ──────────────────────────
    ' Catalogs every date-like value across every sheet
    ' into a report, then standardizes all to one
    ' user-chosen format. Handles both real dates with
    ' inconsistent number formats AND text strings that
    ' look like dates (ERP exports, client schedules).
    ' ──────────────────────────────────────────────────

    Const OUT_SHEET As String = "Date-Format-Report"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, cell As Range, rpt As Worksheet
    Dim outRow As Long, totalDates As Long
    Dim fmtChoice As String, changed As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Create report sheet ────────────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rpt = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    rpt.Name = OUT_SHEET

    rpt.Range("A1:E1").Value = Array( _
        "Sheet", "Cell", "Current Value", _
        "Current Type", "Current Format")
    rpt.Range("A1:E1").Font.Bold = True
    outRow = 2

    ' ── First pass: catalog all date-like cells ─────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet

        For Each cell In ws.UsedRange
            If IsEmpty(cell) Then GoTo NextCell

            If IsRealDateCell(cell) Then
                rpt.Cells(outRow, 1) = ws.Name
                rpt.Cells(outRow, 2) = cell.Address(False, False)
                rpt.Cells(outRow, 3) = cell.Text
                rpt.Cells(outRow, 4) = "Real Date"
                rpt.Cells(outRow, 5) = cell.NumberFormat
                totalDates = totalDates + 1
                outRow = outRow + 1

            ElseIf IsTextDateCell(cell) Then
                rpt.Cells(outRow, 1) = ws.Name
                rpt.Cells(outRow, 2) = cell.Address(False, False)
                rpt.Cells(outRow, 3) = cell.Text
                rpt.Cells(outRow, 4) = "Text Date"
                rpt.Cells(outRow, 5) = "Text (needs conversion)"
                totalDates = totalDates + 1
                outRow = outRow + 1
            End If

NextCell:
        Next cell

NextSheet:
    Next ws

    ' ── Format report ──────────────────────────────────
    rpt.Columns("A:E").AutoFit
    rpt.Range("A2").Select
    ActiveWindow.FreezePanes = True

    If totalDates = 0 Then
        MsgBox "No date-like values found.", vbInformation
        GoTo CleanUp
    End If

    ' ── Choose target format ───────────────────────────
    fmtChoice = InputBox( _
        "Target date format:" & vbCrLf & vbCrLf & _
        "1 = mm/dd/yyyy   (US)" & vbCrLf & _
        "2 = dd/mm/yyyy   (International)" & vbCrLf & _
        "3 = yyyy-mm-dd   (ISO)" & vbCrLf & _
        "4 = mmm dd yyyy  (Long text)", _
        "Date Format Normalizer", "1")

    If fmtChoice = "" Then GoTo CleanUp

    Select Case fmtChoice
        Case "1": fmtChoice = "mm/dd/yyyy"
        Case "2": fmtChoice = "dd/mm/yyyy"
        Case "3": fmtChoice = "yyyy-mm-dd"
        Case "4": fmtChoice = "mmm dd yyyy"
        Case Else
            MsgBox "Enter 1, 2, 3, or 4.", vbExclamation
            GoTo CleanUp
    End Select

    ' ── Confirm ────────────────────────────────────────
    If MsgBox("Found " & totalDates & " date-like value(s)." & _
              vbCrLf & vbCrLf & _
              "Standardize all to '" & fmtChoice & "'?", _
              vbYesNo + vbQuestion, "Confirm") = vbNo Then _
              GoTo CleanUp

    ' ── Second pass: standardize ──────────────────────
    changed = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet2

        For Each cell In ws.UsedRange
            If IsEmpty(cell) Then GoTo NextCell2

            If IsRealDateCell(cell) Then
                cell.NumberFormat = fmtChoice
                changed = changed + 1

            ElseIf IsTextDateCell(cell) Then
                On Error Resume Next
                Dim d As Date
                d = CDate(cell.Value)
                If Err.Number = 0 Then
                    cell.Value = d
                    cell.NumberFormat = fmtChoice
                    changed = changed + 1
                End If
                On Error GoTo CleanUp
            End If

NextCell2:
        Next cell

NextSheet2:
    Next ws

    MsgBox "Standardized " & changed & " date(s) to " & _
           fmtChoice & "." & vbCrLf & vbCrLf & _
           "Report on '" & OUT_SHEET & "' sheet.", _
           vbInformation, "Date Format Normalizer"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helpers ───────────────────────────────────────
Private Function IsRealDateCell(cell As Range) As Boolean
    If VarType(cell.Value) = vbError Then Exit Function
    If Not IsNumeric(cell.Value) Then Exit Function
    If Not IsDate(cell.Value) Then Exit Function
    If cell.NumberFormat = "General" Then Exit Function
    If cell.NumberFormat = "@" Then Exit Function
    IsRealDateCell = True
End Function

Private Function IsTextDateCell(cell As Range) As Boolean
    Dim s As String
    If VarType(cell.Value) <> vbString Then Exit Function
    s = Trim(cell.Value)
    If Len(s) = 0 Then Exit Function
    If IsNumeric(s) Then Exit Function
    ' Match typical date patterns or let IsDate decide
    IsTextDateCell = IsDate(s)
End Function

#How It Works

#Two passes: report first, then act

The macro runs two complete sweeps through every sheet. The first pass catalogs every date-like cell into the report sheet — it counts them, categorizes them as “Real Date” or “Text Date,” and captures the original value and format. Only after you’ve reviewed the report and chosen a target format does the second pass make any changes. You always know exactly what you’re about to modify.

This two-pass design means you can cancel at any point before the standardization runs and lose nothing. The report stays in the workbook as documentation if you want it.

#Real dates vs. text dates — the detection logic

The macro uses two helper functions to classify every cell:

IsRealDateCell — the cell is a numeric value (an Excel date serial number) that IsDate recognizes as a date AND has a non-trivial number format. The NumberFormat check excludes the General format (which could be any number) and @ (the explicit text format). If you formatted a cell with Ctrl+1 → Number → Date → mm/dd/yyyy, it passes this check.

If Not IsNumeric(cell.Value) Then Exit Function
If Not IsDate(cell.Value) Then Exit Function
If cell.NumberFormat = "General" Then Exit Function

IsTextDateCell — the cell contains a string that VBA’s IsDate function recognizes as a date. This catches "2026-06-15", "15-Jun-2026", "06/15/2026", and dozens of other date-like string formats. It skips pure numbers (those would have been caught by IsRealDateCell if they had a date format) and empty strings.

If VarType(cell.Value) <> vbString Then Exit Function
s = Trim(cell.Value)
If IsNumeric(s) Then Exit Function
IsTextDateCell = IsDate(s)

#The InputBox: pick your format by number

Rather than asking the user to type a number format string (which they’d get wrong), the InputBox presents four numbered options:

1 = mm/dd/yyyy   (US)
2 = dd/mm/yyyy   (International)
3 = yyyy-mm-dd   (ISO)
4 = mmm dd yyyy  (Long text)

The Select Case block converts the user’s number into the actual format string. If the user types anything other than 1–4, they get a clean error message and the macro exits without touching data.

#Text date conversion uses CDate with a safety net

Converting a text string like "2026-06-15" to a real date uses VBA’s CDate() function, which is remarkably good at parsing date strings. But it can fail on truly ambiguous inputs (is "03/04/2026" March 4 or April 3?).

On Error Resume Next
Dim d As Date
d = CDate(cell.Value)
If Err.Number = 0 Then
    cell.Value = d
    cell.NumberFormat = fmtChoice
    changed = changed + 1
End If
On Error GoTo CleanUp

The On Error Resume Next swallows conversion failures on a per-cell basis. If CDate throws — because the text isn’t actually a recognizable date after all — the cell is silently skipped and the error handler resets to CleanUp for the next cell. The original text value remains untouched.

#Why NumberFormat alone for real dates

When a cell already contains a real Excel date (serial number), the macro only changes its NumberFormat property. This is important because:

1. It’s instant. No type conversion needed — the underlying value is already correct.
2. It’s visually verifiable. Change the format, and the displayed value changes immediately. If you standardize 01/15/2026 (mm/dd) to dd/mm/yyyy and it shows 15/01/2026, you know the underlying date was correct.
3. It doesn’t risk date ambiguity. Unlike text dates where "03/04/2026" could be two different dates, a real date serial number is unambiguous. The format only changes how it’s displayed, not what it is.

#The cleanup label covers both passes

Every On Error GoTo CleanUp routes to the same label at the bottom. Whether the error occurs during the cataloging pass or the standardization pass, the cleanup restores ScreenUpdating and Calculation and shows a message box with the error details. A crashed macro that leaves Excel in manual calculation mode is a support ticket generator — the cleanup prevents it.

#Adapt It