How to convert dates in excel to common format.

Hi Alex70​, 

SAP exports often produce dates in mixed formats, which can make Excel misinterpret day and month. Here is a solution to standardize your columns E and G (and any other SAP date columns) so Column G is read as January 2, not February 1.

Step 1: Check if the dates are text or real Excel dates

Use:

excel

=ISNUMBER(E2)

=ISNUMBER(G2)

If the result is TRUE, Excel already recognizes it as a date. If FALSE, it’s text and needs conversion.

Step 2: Use Excel 365 formula to convert text dates

In a helper column:

excel

=IF(ISNUMBER(E2), E2,

  LET(

    raw, TRIM(E2),

    s, SUBSTITUTE(SUBSTITUTE(raw, ".", "/"), "-", "/"),

    pos1, FIND("/", s),

    pos2, FIND("/", s, pos1+1),

    d, VALUE(LEFT(s, pos1-1)),

    m, VALUE(MID(s, pos1+1, pos2-pos1-1)),

    y, VALUE(RIGHT(s, LEN(s)-pos2)),

    DATE(y, m, d)

  )

)

This works for DD.MM.YYYY, DD/MM/YYYY, and forces day/month/year order. Repeat for Column G if needed.

Step 3: Format the result

Select Columns E and G → Right-click → Format Cells → Custom → Enter:

Code

DD-MMM-YYYY

Example: 02-Jan-2025

Step 4: Automate with VBA (for future SAP imports)

vba

Sub StandardizeAllSAPDates()

    Dim ws As Worksheet, cell As Range, col As Range

    Dim parts() As String, d As Long, m As Long, y As Long

    Set ws = ActiveSheet

    For Each col In ws.UsedRange.Columns

        For Each cell In col.Cells

            If Not IsEmpty(cell.Value) Then

                If Not IsDate(cell.Value) Then

                    cell.Value = Replace(Replace(cell.Value, ".", "/"), "-", "/")

                    parts = Split(cell.Value, "/")

                    If UBound(parts) = 2 Then

                        d = CLng(parts(0))

                        m = CLng(parts(1))

                        y = CLng(parts(2))

                        cell.Value = DateSerial(y, m, d)

                    End If

                End If

                cell.NumberFormat = "DD-MMM-YYYY"

            End If

        Next cell

    Next col

    MsgBox "All SAP dates standardized successfully!"

End Sub

Usage: Press ALT + F11 → Insert → Module → Paste the code → Run StandardizeAllSAPDates

This ensures mixed text and serial dates are standardized, Column G is read correctly as January 2, and future imports can be fixed automatically.