How to fix expense CSV dates and amounts

Keep the original CSV and diagnose one issue at a time: the separator, date convention, decimal convention or currency. Check the meaning before using the imported rows in a report.

These problems can occur in a local spreadsheet or a cloud spreadsheet. The examples below use Google Sheets, with links to Excel's own import controls. They concern importing existing rows; extracting fields from a receipt image or PDF is a separate step.

Download the import-format practice CSV. It deliberately contains text needing review and separate expected values whose meaning has been stated in the fictional source notes. Never assume the same interpretation for an ambiguous date in your own data.

1. Import into a staging copy

Save the source unchanged. In Google Sheets, use File → Import, select the file and choose a destination that keeps your working records intact. Inspect the separator setting, import into a new staging sheet and check the resulting columns. A quoted amount containing a comma should remain one field rather than shifting the columns after it.

Google's import instructions explain the available separator and destination options. Options depend on the file type; importing an Excel workbook is not the same operation as appending CSV rows.

In Excel, use the documented text/CSV import workflow when you need control over how fields are interpreted. Simply opening a CSV can apply default date and number interpretation before you have checked it.

2. Establish what each source value means

The first two strings look like different dates until you know the source convention. Check the actual document or export specification. If the meaning is unknown, mark the row for review instead of converting every date under one assumption.

Google Sheets' File → Settings locale affects default date, currency and number formatting. Google's locale documentation explains that scope. Changing the locale does not prove which date the original supplier meant.

3. Store real dates and numeric amounts

Once the date is confirmed, enter it as a spreadsheet date and use an unambiguous display such as 2026-09-05. For a small practice correction, =DATE(2026,9,5) creates that date without relying on the interpretation of a slash-separated string.

Keep currency in its own column. Enter the confirmed amount as a number in the spreadsheet's expected decimal format. Do not remove every comma or period indiscriminately: that could turn a decimal amount into a value one hundred times larger.

Use Format → Number to select the appropriate display after the value is stored correctly. Google's number-format guide covers date and currency displays. Formatting text as currency does not necessarily convert it to a numeric value, and selecting USD as a display format does not convert AUD into USD.

4. Check the corrected rows before appending them

Count the imported rows and inspect the first, middle and last records. Check several dates against the originals, confirm amounts can be summed, and compare currencies separately. Keep IDs and receipt references as identifiers, including any meaningful leading zeros.

Look for a totals row accidentally imported as another purchase. Check whether an overlapping date range has already been loaded. The duplicate review guide helps distinguish repeated imports from legitimate repeat purchases.

For this small exercise, compare each corrected date and amount with its explicit expected-value columns. Those columns are teaching answers, not something an import tool could safely infer from every real-world file.

5. Keep the corrected source usable elsewhere

Retain the original, corrected copy and a short note explaining material changes. Use an XLSX workbook when you need multiple tabs and formulas. Use CSV for exchanging a single table, and inspect the exported copy before handing it to another system.

The monthly report example starts with already reviewed rows. If your starting point is a receipt photo or PDF, the receipt-to-CSV converter produces editable fields to check before import. It does not repair existing CSV uploads or automatically save the rows to a connected Sheet.

Frequently asked questions

Will changing the cell format fix an ambiguous date?

No. Confirm the source meaning first, then store a real date. A display format cannot tell you whether 05/09 meant May 9 or September 5.

Does a currency format convert the money?

No. Keep the original currency explicit. Formatting a number with another currency symbol does not apply an exchange rate.