# Fix Dates in Google Sheets After a CSV Import

- Author: FastBankConvert (https://www.fastbankconvert.com)
- Published: 2026-08-14
- Updated: 2026-08-14
- Category: Google Sheets, Numbers and LibreOffice
- Canonical: https://www.fastbankconvert.com/blog/fix-dates-in-google-sheets

> Fix dates in Google Sheets that import as text or swap day and month: the spreadsheet locale, DATEVALUE, and splitting dates apart.
To fix dates in Google Sheets after a CSV import, set the spreadsheet locale to match the file's date order and import again. For dates already stuck as text, convert them with DATEVALUE. For day and month that came in swapped, split the parts and rebuild each date with DATE, then confirm the result.

## Why do CSV dates land in Google Sheets as text?

Google Sheets reads a CSV date by the spreadsheet's locale. When the text does not fit a pattern that locale knows, Sheets keeps it as plain text. A date like 25/03/2026 means nothing to a United States locale, because there is no 25th month, so it stays text.

The awkward part is that not every row fails. Under a month-first locale, 03/04/2026 reads fine as March 4, while 25/03/2026 on the next row stays text. You end up with a column that is half real dates in the wrong month and half text. Sorting that column puts rows in a strange order, and a monthly total misses everything that stayed text.

Other causes are simpler. A date with a leading apostrophe, a trailing space, or a month name in another language also stays text. So does a date the bank printed without a year, such as "Mar 14".

## Setting the spreadsheet locale before a date import

The locale is the setting that decides whether Sheets reads 03/04 as March 4 or 3 April. Google's help on [changing a spreadsheet's locale and time zone](https://support.google.com/docs/answer/58515) puts it under File > Settings, on the General tab, and says the locale changes the default currency, date and number formatting.

Set it before importing:

1. Open a new spreadsheet and go to File > Settings.
2. Under Locale, pick a country that writes dates the way your file does. United Kingdom reads day first; United States reads month first.
3. Choose Save settings.
4. Go to File > Import and bring the CSV in.

Change the locale after the import and the text cells stay text. Sheets has already decided what each cell is, so delete the data and import it again. The same trap exists in Apple's spreadsheet app, and the guide to [opening a CSV in Numbers](/blog/open-csv-in-numbers) covers its region setting.

## Converting text dates in Sheets with DATEVALUE

DATEVALUE turns a text date into a real date when the text is in a format the locale already accepts. Put `=DATEVALUE(A2)` in a spare column and fill it down. Google's [DATEVALUE help page](https://support.google.com/docs/answer/3093039) notes that it returns a serial number, so format the column as a date afterwards.

Use DATEVALUE for text that is correct but was stored wrongly, for example dates with a stray space, or ones typed after a leading apostrophe. Wrap it with TRIM when spaces are the cause: `=DATEVALUE(TRIM(A2))`. Once the new column looks right, copy it and use Edit > Paste special > Values only over the original.

DATEVALUE does not fix a wrong day order. Under a month-first locale it will happily read "03/04/2026" as March 4 even if the bank meant 3 April. It also returns an error when the cell already holds a real date, because it expects text.

## Rebuilding swapped day-month dates with DATE and SPLIT

When the file uses a different date order from the spreadsheet and you cannot import again, take each date apart and rebuild it. The DATE function takes year, month and day as separate numbers, so you control which part is which instead of leaving it to the locale.

For a day-first text date in A2, such as 25/03/2026, use:

`=DATE(INDEX(SPLIT(A2, "/"), 3), INDEX(SPLIT(A2, "/"), 2), INDEX(SPLIT(A2, "/"), 1))`

SPLIT breaks the text at each slash, INDEX picks the piece, and DATE puts them in the right slots. This only works on cells that are still text. If some rows already became real dates in the wrong month, rebuild those from their parts instead: `=DATE(YEAR(A2), DAY(A2), MONTH(A2))` swaps the day and month back.

### Worked example

Example rows from a day-first CSV imported under a United States locale:

| Raw text in the CSV | What Sheets stored | Fix |
| --- | --- | --- |
| 03/04/2026 | March 4, 2026 (wrong month) | `=DATE(YEAR(A2), DAY(A2), MONTH(A2))` |
| 25/03/2026 | Text "25/03/2026" | DATE with SPLIT, as above |
| 2026-03-25 | March 25, 2026 | Already correct; ISO order reads the same everywhere |
| 25/03/2026 (with trailing space) | Text | `TRIM` first, then DATE with SPLIT |

The third row is a reason to ask for year-month-day output when you have the choice. That order is unambiguous, so no locale misreads it. Billing documents raise the same issue with service periods, which the guide to [converting a utility bill PDF to Excel](/blog/utility-bill-pdf-to-excel) covers.

FastBankConvert, a statement converter that is getting ready to open, is built to give each transaction date its own column in the download. You can [sign up through Get started](/get-started) to hear when trial conversions begin.

## How do you test that a Sheets date column is fixed?

A date column is fixed when every cell holds a real date and each date sits in the right month. Test three things: alignment, a type formula, and the date range. Together they catch both the text cells and the silently swapped ones.

1. **Alignment.** Real dates align right by default. Any left-aligned cell is still text.
2. **Type check.** In a spare column, `=ISNUMBER(B2)` returns TRUE for a real date, because Sheets stores dates as numbers. Filter for FALSE.
3. **Range check.** `=MIN(B2:B200)` and `=MAX(B2:B200)` should fall inside the statement period. A December date in a March statement is a swapped day and month.

Run the range check even when the first two pass. A swapped date is still a valid date, so only the period gives it away. If you are moving the same data into Apple's app, the guide to [getting a PDF into Numbers](/blog/pdf-to-numbers) includes a similar check for that program.