A date like 03/04/2026 is ambiguous: a system set to day-month-year reads it as 3 April, while one set to month-day-year reads it as 4 March. Dates above the 12th, such as 25/12/2026, can only be read one way, so they are your test. Convert text dates to real date values and export them in a format that cannot be misread, such as yyyy-mm-dd.
How do you tell whether your dates are real dates?
- Real dates align to the right of the cell and change when you apply a different date format. Text dates align to the left.
- =ISNUMBER(A2) returns TRUE for real dates and FALSE for text.
- =COUNTA(A2:A500)-COUNT(A2:A500) tells you how many are text; 0 is the goal.
- Dates after the 12th that show as text are a sign that the file or your system expected month-day-year.
How do you test with a few rows before the full import?
Make a small file with three rows and import it first: 03/04/2026, 13/04/2026 and 25/12/2026. If the first lands on 3 April, the day-month order was read correctly. If it lands on 4 March, or the second row fails or turns to text, the order is being swapped. Delete the test rows and fix the format before touching the real data.
Keep that three-row file. Whenever you import from a new bank, card or old sheet, run it through first. It takes a minute, and it shows you the day-month order for that source before it can quietly flip every date in a year of data.
How do you convert text dates?
There are three methods. Choose the one that fits your data.
- Text to Columns (Excel): select the date column, choose Data, Text to Columns, Delimited, Next, Next, then under Column data format select Date and choose DMY. Finish. This converts in place.
- DATE with text functions, when the dates are fixed-length like 05/11/2026: =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))). This ignores the system setting, so it is the safest.
- DATEVALUE: =DATEVALUE(A2). This follows your computer's regional setting, so it only works if that setting matches the text. Check one result that is above the 12th.
Dates without leading zeros, such as 5/11/2026, break the fixed-position formula above. Use =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)),VALUE(LEFT(A2,FIND("/",A2)-1))) instead, which finds the slashes wherever they are.
What if dates have month names or times?
Dates such as 14 Oct 2026 or 14-Oct-26 are less ambiguous, because the month is spelled out. DATEVALUE often reads them directly in an English-language setting, but check one result. A date with a time attached, such as 14/10/2026 18:45, holds a decimal part; remove it with =INT(A2) once the cell is a real date, then format the result as a date. If some rows have times and others do not, the column is mixed, so convert the whole column the same way and look at the first and last rows to confirm.
What format should you export?
Copy the converted dates, paste them as values over the original column, then format the column as yyyy-mm-dd through Format cells, Custom. A CSV stores what the cell displays, so with the format set you export 2026-04-03, which no program mistakes for 4 March. In Google Sheets the same format sits under Format, Number, Custom date and time.
A second look at the review screen
Even after a clean export, read the dates on the review screen before saving. SpendixAI shows rows for review before they are saved, so look at the first row of each month and the last row, and make sure the day and month match the original. Bank statements print their own date order, so if you upload a statement itself, compare the first rows with the original PDF in the same way.
Quick checklist
- Test file with 03/04/2026, 13/04/2026 and 25/12/2026 imports correctly.
- ISNUMBER returns TRUE down the whole column.
- Dates pasted as values in yyyy-mm-dd format.
- No two-digit years.
- Spot check: first and last date of each month after import.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


