Tech Fix Daily

Some dates are in MM/DD/YYYY format (e.g. 09/11/2024 → Nov 9 or Sep 11 depending on system).

Some dates are in DD/MM/YYYY format (e.g. 13/09/2024 → 13 Sep 2024).

=IF(ISNUMBER(A1),A1,DATEVALUE(TEXT(A1,"DD/MM/YYYY")))

’ll give you complete, step-by-step solutions (several methods) you can pick from depending on how comfortable you are with Excel, Power Query or VBA. Each method includes exactly what to click/type so you can convert mixed MM/DD/YYYY and DD/MM/YYYY dates into a single consistent DD/MM/YYYY (day-first) column — and how to handle ambiguous entries (like 09/11/2024 which could be 9 Nov or 11 Sep).

Important first step — backup

1. Make a copy of your workbook (File → Save As) before doing any mass-change.

---

METHOD A — Fast GUI fix: Text → Columns (best when most values follow one format)

Use when most entries are day-first (DD/MM/YYYY) or month-first (MM/DD/YYYY) consistently.

1. Insert a helper column to the right of your dates column.

2. Select the original date column.

3. Data → Text to Columns.

4. Choose Delimited → Next → Next.

5. Under Column data format choose Date and pick:

   • DMY if you want day/month/year (UK style), or

   • MDY if you want month/day/year (US style).

6. Finish.

Excel will parse the text into real dates. Format the column as dd/mm/yyyy:

• Home → Number Format → More Number Formats → Date dd/mm/yyyy.

If you have mixed formats in the same column, Text to Columns will not reliably fix ambiguous rows — use Method B, C or D below.

---

METHOD B — Power Query (recommended; robust & visual)

Power Query lets you parse every row with custom logic and flag ambiguous dates.

Step-by-step (Excel 2016 / Office 365):

1. Select your date column (convert it to a table first: Home → Format as Table).

2. With a cell selected in the table: Data → From Table/Range.

3. In Power Query Editor:

   • Right-click the date column → Split Column → By Delimiter / → split into 3 columns (Column.1, Column.2, Column.3).
     (If delimiter is - or ., choose that.)

4. Convert the three new columns to Whole Number: select them → Transform → Data Type → Whole Number.

5. Add a new custom column: Add Column → Custom Column and paste this formula (it creates a real date, assuming you want day-first (DD/MM/YYYY) by default, but it intelligently uses month-first when necessary):

= let
  p1 = [Column.1],
  p2 = [Column.2],
  p3 = [Column.3]
in
  if p1 > 31 or p2 > 31 then null
  else if p1 > 12 then #date(p3, p2, p1)            // if first part >12 → DAY is p1 => DMY
  else if p2 > 12 then #date(p3, p1, p2)            // if second part >12 → MONTH is p2 => MDY
  else  // both ≤12 => ambiguous → choose DMY by default
    #date(p3, p2, p1)

6. Name the new column FixedDate. It will contain real Date values.

7. (Optional) Add a flagged column for ambiguous rows: Add Column → Custom Column:

= if [Column.1] <= 12 and [Column.2] <= 12 then "Ambiguous" else null

8. Remove the original split columns if you don’t need them.

9. Home → Close & Load back to Excel.

Now format FixedDate in Excel as dd/mm/yyyy. Review any rows flagged "Ambiguous" (Power Query created them for manual check).

---

METHOD C — Formula approach (no Power Query / no VBA)

This uses helper columns and is good if you want to see logic row-by-row.

Assume your raw dates are in column A (A2 start). Put formulas in helper columns B,C,D:

1. In B2 (extract first part):

=VALUE(TRIM(LEFT(A2, FIND("/",A2&"/")-1)))

2. In C2 (middle):

=VALUE(TRIM(MID(A2, FIND("/",A2&"/")+1, FIND("/",A2&"/", FIND("/",A2&"/")+1) - FIND("/",A2&"/") -1)))

3. In D2 (year - last part):

=VALUE(TRIM(RIGHT(A2, LEN(A2)-FIND("^^", SUBSTITUTE(A2,"/","^^", LEN(A2)-LEN(SUBSTITUTE(A2,"/","")) )) )))

(The D formula is a bit clunky due to varying lengths; if your year is always 4 digits you can use:

=VALUE(RIGHT(A2,4))
```)

4. Now create the normalized date in E2:
```excel
=IFERROR(
  IF(B2>12, DATE(D2,C2,B2),   IF(C2>12, DATE(D2,B2,C2), DATE(D2,C2,B2)) ),
  ""
)

Explanation:

• If first part >12 → must be day-first → DATE(year, month=C2, day=B2).

• Else if second part >12 → second part is day → month=B2, day=C2.

• Else both ≤12 (ambiguous) → default to DMY (you can change to MDY by swapping).

5. Format column E as dd/mm/yyyy.

6. Filter column E for blanks or odd values — check and fix manually ambiguous rows.

Note: The TEXT splitting formulas assume / delimiter. For - or . adapt accordingly.

---

METHOD D — VBA macro (automatic, flags ambiguous rows)

This macro:

• Scans a selected column.

• For each cell that is text, splits on / (or -), converts to date using detection logic, and writes a normalized date in adjacent column.

• Marks ambiguous rows for manual review.

How to run:

1. Press Alt+F11 → Insert → Module → paste the code below.

2. Close Editor. Select the date column or just place cursor on a cell in column A.

3. Run macro: Developer → Macros → NormalizeMixedDates → Run.

VBA code:

Sub NormalizeMixedDates()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A2:A" & lastRow) ' adjust if header or different column

    Dim outCol As Long: outCol = 2 ' writes normalized date to column B
    ws.Cells(1, outCol).Value = "FixedDate"
    ws.Cells(1, outCol + 1).Value = "Note"

    Dim parts() As String
    Dim p1 As Long, p2 As Long, p3 As Long
    Dim dt As Date, ambiguous As Boolean
    Dim i As Long

    For Each cell In rng
        If Trim(cell.Value) <> "" Then
            ambiguous = False
            parts = Split(Replace(cell.Value, "-", "/"), "/")
            If UBound(parts) = 2 Then
                On Error Resume Next
                p1 = CLng(parts(0)): p2 = CLng(parts(1)): p3 = CLng(parts(2))
                If Err.Number <> 0 Then
                    ws.Cells(cell.Row, outCol).Value = ""
                    ws.Cells(cell.Row, outCol + 1).Value = "Parse error"
                    Err.Clear
                    On Error GoTo 0
                Else
                    On Error GoTo 0
                    ' decide which format
                    If p1 > 31 Or p2 > 31 Then
                        ws.Cells(cell.Row, outCol).Value = ""
                        ws.Cells(cell.Row, outCol + 1).Value = "Invalid date parts"
                    ElseIf p1 > 12 Then
                        dt = DateSerial(p3, p2, p1) ' DMY
                        ws.Cells(cell.Row, outCol).Value = dt
                        ws.Cells(cell.Row, outCol + 1).Value = "DMY"
                    ElseIf p2 > 12 Then
                        dt = DateSerial(p3, p1, p2) ' MDY
                        ws.Cells(cell.Row, outCol).Value = dt
                        ws.Cells(cell.Row, outCol + 1).Value = "MDY"
                    Else
                        ' both <=12 => ambiguous. Choose DMY by default but flag it
                        dt = DateSerial(p3, p2, p1)
                        ws.Cells(cell.Row, outCol).Value = dt
                        ws.Cells(cell.Row, outCol + 1).Value = "Ambiguous (assumed DMY)"
                    End If
                    ws.Cells(cell.Row, outCol).NumberFormat = "dd/mm/yyyy"
                End If
            Else
                ws.Cells(cell.Row, outCol).Value = ""
                ws.Cells(cell.Row, outCol + 1).Value = "Wrong format"
            End If
        End If
    Next cell

    MsgBox "Done. Fixed dates in column " & Split(Cells(1, outCol).Address, "$")(1) & ". Review 'Note' column for ambiguous rows.", vbInformation
End Sub

Notes:

• Adjust range "A2:A" if your dates are in another column or start row.

• The macro writes normalized dates into column B and notes into column C.

• Ambiguous rows are marked "Ambiguous (assumed DMY)" — manually review these.

---

METHOD E — Detect which format predominates and apply automatically

If your sheet has a mix but mostly one format, this two-step approach works:

1. Add formula to detect DMY vs MDY prevalence:

   • In helper column, compute whether first part >12 → count these as DMY clues; second part >12 → MDY clues.

2. If DMY clues >> MDY clues, then run a Text to Columns Date=DMY on the whole column (or use Power Query to choose DMY).

3. After conversion, filter and fix rows that failed.

---

EXTRA: How to set Windows / Excel regional format (so future entries use day-first)

1. Control Panel → Region → Formats → choose English (United Kingdom).

2. Click Additional settings → Date and set short date to dd/MM/yyyy.

3. Restart Excel.

For Excel import behavior, use Data → Get Data → From Text/CSV and select Locale to English (United Kingdom) when parsing.

---

QUICK CHECKLIST (do this now)

1. Make a copy of workbook.

2. Inspect a few problematic cells: are they left-aligned (text) or right-aligned (true date)? (If left → need conversion.)

3. If most follow one format → use Text to Columns.

4. If mixed → use Power Query method (recommended) or the VBA macro (automatic).

5. Filter for flagged/ambiguous rows and fix manually.

6. After converting, format the result column as dd/mm/yyyy.