Imported Dates Swap Day and Month: Check Them Before ERP Loading
No error does not mean the same date
Hypothetical example: a delivery record contains 03/04/2026, meaning 3 April at the source. An importer reads month/day/year and produces March 4. Both are valid dates, so it may report no error. Document counts and totals still match while monthly reporting or delivery dates are wrong. The parsing rule needs correction, not merely the column’s appearance.
Before loading ERP data, identify whether each field is a document date, shipment date, posting date or due date. Similar-looking values serve different purposes. The business owner should confirm meaning before technical rules are configured; a column simply named date is insufficient specification.
Separate parsing from display
The text 03/04/2026 needs a rule defining its day, month and year. After conversion to a date type, the value represents a particular day; displaying it as 3 Apr or 2026-04-03 is another step. If it was parsed as March 4, changing the display to year-month-day merely shows the wrong date in a new format.
Microsoft explains that Power Query uses locale when interpreting text as typed values, with an option specific to a type conversion. This illustrates what to check in a tool, not a claim that every ERP has the same menu. If an Excel file already stores actual date values, inspect the source type first instead of unnecessarily converting every field back to text.
Specify the source before choosing locale
Write a specification for each source: day-month-year order, Buddhist or Gregorian year convention, meaning of blanks and representation of unknown values. Do not automatically subtract 543 from every year in a mixed file. Do not substitute the first day of a month for an unknown date without approval; that invents precision absent from the source.
In Power Query, inspect Applied Steps that change data types. For source text following UK day/month/year, Change Type → Using Locale can specify Date and English (United Kingdom). Inspect earlier conversions too: setting locale later cannot recover original intent if day and month have already been swapped and saved.
Test visible errors and silent swaps
Include ambiguous dates such as 03/04/2026 and dates with a day above 12 such as 23/04/2026, with expected values approved by the data owner. Add blanks, impossible dates and relevant month-boundary cases to see whether the system rejects, holds or substitutes values. Testing only today’s date is inadequate.
After conversion, extract year, month and day and compare them with the original, so display formatting cannot mask the value. Also check monthly placement or due dates for sample documents. Record document ID, raw value, parsed value and applied rule for mismatches. Fixing isolated destination cells can hide a pipeline that will fail again next time.
Correct the import with a traceable original
Keep raw files and each run’s rules separately before rerunning. Identify unresolved records and establish whether another import updates existing entries or creates new ones. If dates have already driven cut-offs, shipments or accounting postings, the process owner must assess impact before corrections. Do not backdate records or reopen periods merely to make reports match.
When discussing ERP implementation or integration with Chawinnix, prepare samples with personal information removed, source descriptions, date rules and expected results. Acceptance should mean dates are semantically correct and usable in the workflow, not just that the importer displays success.
