# Xero CSV Date Error: Day and Month Mixed Up - Author: SimpleBankConvert (https://www.simplebankconvert.com) - Published: 2026-04-29 - Updated: 2026-04-29 - Category: Import errors - Canonical: https://www.simplebankconvert.com/blog/xero-csv-date-error > How to fix a Xero CSV date error: why Xero misreads day and month, how to set one date format in the file and how to check it before upload. A Xero CSV date error happens when the date column can be read two ways, such as 03/04/2026, or when rows use different formats. Xero then swaps day and month, or rejects the rows. Fix it by giving every row one date format with a four-digit year, and by testing a date above the 12th before uploading. ## Why a Xero CSV date error swaps day and month A date like 03/04/2026 is ambiguous. In the United States it means March 4. In the UK, Australia and New Zealand it means 3 April. A CSV file carries no label saying which order the writer meant, so the program reading it has to assume one. When the assumption differs from the bank's, every early-month date flips. The tell is a pattern, not a single wrong row. Dates from the 1st to the 12th of each month land in the wrong month, while dates from the 13th onward either import correctly or fail, because 15/03 cannot be read as month 15. If your Xero import shows half a statement in the right place and the rest scattered across the year, this is almost always the cause. ## Which CSV date formats trip up a Xero statement import? Four kinds of date cause most failures. Ambiguous numeric dates are the first. The others are a column that mixes formats, dates stored as text with extra words, and two-digit years. Each one is easy to spot once you know to look for it. - **Ambiguous numeric dates.** 05/06/2026 could be either of two days a month apart. - **Mixed formats.** One row reads `2026-03-09`, the next `Mar 4, 2026`, a third `4/3/26`. Bank exports and copy-and-paste from a PDF both produce this. - **Text dates with extras.** A weekday in the same cell, such as `Tue 03/03/2026`, or a trailing space, turns the cell into text. - **Two-digit years.** `03/04/26` adds a third way to misread the same cell. Excel adds a hidden risk. When you open a CSV, Excel converts what it thinks are dates using your computer's regional settings, and saving the file writes them back in that new form. A file that was consistent when the bank produced it can be scrambled by a single open-and-save. ## Setting one date format down the Xero CSV date column The fix is one format for every row, with the day, month and year in a fixed order and a four-digit year. Choose the order that matches how your Xero organisation shows dates. Then make sure no cell in the column holds anything except the date. In Excel or Google Sheets: 1. Insert a blank column next to the date column. 2. If the dates are real dates, use a formula such as `=TEXT(A2,"dd/mm/yyyy")` for day-first, or `"mm/dd/yyyy"` for month-first, and fill it down. 3. If some dates are text, split out the weekday or extra words first with find and replace, then convert. 4. Copy the new column and paste it back over the old one as values only. 5. Delete the helper column and save as CSV. Keep the amount column in the same clean state while you are in there. A currency symbol or a bracketed negative breaks an import just as surely as a bad date, and the guide to the [QuickBooks CSV amount column error](/blog/quickbooks-csv-amount-column-error) lists the amount problems that apply to any accounting CSV. ## How do you test Xero CSV dates before the upload? Test with a date after the 12th of the month. A day such as the 15th can only be read one way, so it tells you which order your file really uses. Then check that every row follows that order. This takes two minutes and saves deleting a full statement from Xero later. ### A worked example The rows below are made-up examples from a March statement where the bank wrote dates month-first. | Raw cell | Read month-first | Read day-first | Fixed value (day-first) | | --- | --- | --- | --- | | 03/04/2026 | March 4 | 3 April | 04/03/2026 | | 03/15/2026 | March 15 | invalid | 15/03/2026 | | Mar 9, 2026 | March 9 | March 9 | 09/03/2026 | | 3/28/26 | March 28 | invalid | 28/03/2026 | The second row proves the bank's order. Because 15 cannot be a month, the whole column is month-first, and the first row means March 4. ### Pre-upload checklist - Every date sits inside the statement period. - Every row uses the same format and a four-digit year. - At least one date after the 12th reads correctly in your chosen order. - The file was saved as CSV after the last edit, not reopened and resaved. ## Xero statement lines that landed in the wrong month If the swapped dates already imported, do not try to fix them one at a time. Delete the imported statement lines for that upload, correct the CSV and import it again. Reconciling against lines with wrong dates matches deposits to the wrong invoices and leaves the statement balance out of step with the bank. Before you reimport, confirm the corrected file covers exactly the same date range, so you do not leave a gap or bring in lines twice. If you keep hitting format trouble with CSV, an OFX file stores dates in a fixed year-month-day order that cannot be misread. OFX files have their own failure points, which the post on the [Xero OFX import error](/blog/xero-ofx-import-error) walks through. Statements that print dates without a year need extra care, because the year has to be added from the statement period. The guide to [converting a Wells Fargo bank statement to QBO](/blog/convert-wells-fargo-bank-statement-to-qbo) shows that step for one bank. SimpleBankConvert is being built to put every date in one consistent format and lay the CSV out for Xero's import. It is not open yet, and the [get started page](/get-started) takes your email.