When an expense CSV opens in Excel with dates in the wrong month or reference numbers shown as 1.23E+14, Excel has helpfully converted data it should have left alone. The fix is to import the file and set those columns to Text or to the right date order before loading.
Why does Excel change dates and long numbers?
When you double-click a CSV, Excel looks at each cell and decides what it is. A value that looks like a date becomes a date, using your computer's order. A long string of digits becomes a number, and numbers beyond fifteen digits lose accuracy, so the tail becomes zeros. Both changes are quiet, so the sheet looks tidy while the data is wrong.
| Original in file | What Excel may show | Why it matters |
|---|---|---|
| 03/04/2026 (3 April) | March 4 if read month first | Falls in the wrong month |
| 000458 | 458 | Reference no longer matches the source |
| 4000123456789012 | 4.00012E+15 | Digits are lost for good once saved |
| 12-3 | 12-Mar | Part number turned into a date |
How do you import with control?
- Open a blank workbook.
- Choose Data, then From Text/CSV.
- Select Transform Data to open the editor.
- Select the reference column and set its type to Text.
- Select the date column and set its type to Date using Locale, then choose a day-first English locale.
- Close and load.
Worked check with A$ amounts
Say a file has rows for A$220.00 on 02/05, A$75.50 on 03/05 and A$310.25 on 14/05. The first two dates are the ambiguous ones, since 02/05 could be 2 May or 5 February. The third row, 14/05, cannot be month-first, because there is no month 14. Use rows like that as a clue: if Excel left 14/05 as text while converting the others, the sheet is reading dates month-first. The total is 220.00 + 75.50 + 310.25 = 605.75 whichever way the dates fall, so the total will not warn you; only the date column does.
Can you prevent this in future exports?
- Ask for dates in year-month-day form, which is hard to misread
- Ask for references as text, or with a prefix letter
- Avoid opening and re-saving the CSV in Excel
- Keep a clear header row with one word per column
What does an app add?
An export that already uses one fixed date style and plain columns saves guesswork. SpendixAI is built around India and INR, so it is not aimed at Australian dollar accounts, but it provides CSV downloads of reports and transactions, with one transaction per row.
What if the file was already saved with the damage?
If you opened a CSV, saw 4.00012E+15 and saved, the lost digits cannot be rebuilt from that file. Go back to the source and export again. This is why the habit of keeping the original download matters. Rename it with the date, store it in a folder you do not edit, and always work on a copy. Re-exporting is usually a one-minute job, and it is far better than guessing at digits you can no longer see.
How do you spot the damage early?
Scan three things within a minute of opening any CSV: the widest numeric column for any E+ notation, the first column of IDs for missing zeros, and the date column for any value that looks like a month name you did not expect. A part number such as 12-3 turning into 12-Mar is easy to miss until a lookup fails later. A one-minute scan on opening is cheaper than a rebuild.
Quick checklist
- Import, do not double-click
- Set references to Text
- Set dates with the right locale
- Compare one known date
- Keep the original untouched
Useful ideas start with a clearer picture of your own spending.
Explore your reports
