Search rowmedic

Remove DuplicatesFix Date FormatRepair CSVClean TextCSV ValidatorCSV to ExcelRemove Empty RowsClean Excel DataToolsUse CasesPricingBlogHelpWhich columns make a record unique?Read a broken CSV one record at a timeStandardize dates without swapping the month and day
rowmedic
← All guides

Standardize dates without swapping the month and day

Identify the source date order, distinguish ambiguous values from impossible dates, and verify a spreadsheet date conversion.

An invoice dated 03/04/2025 could belong in March or April. Both readings describe a real day. A successful conversion by itself tells you nothing about which month the source intended.

Find evidence for the source order

A value such as 19/04/2025 can only be day-first. A value such as 04/19/2025 can only be month-first. Check several records from the same export and confirm the convention against the source system’s settings or a known invoice.

An appended file may combine exports from several offices. Evidence from the first office only tells you how to interpret that office’s dates. If the source conventions differ, process each group with its own convention.

Separate ambiguity from invalidity

Source value What you can establish
03/04/2025 Day-first gives 3 April. Month-first gives 4 March.
29/02/2024 A valid leap day under a day-first convention.
29/02/2025 February 2025 has only 28 days.

Consult the source record for the final example. Substituting 28 February or 1 March would assign a date without evidence.

Verify a month boundary

Once you have established the convention, preview conversion to year-month-day. A day-first 03/04/2025 should become 2025-04-03. Check a date on or near the end of a month and compare a few transactions against their original invoices.

If the data feeds monthly reports, compare the counts for adjacent months. A swap can move a transaction between reporting periods while leaving the total number of records unchanged.

Open the date-format tool to inspect the source order and preview the result.