# Change dates in Excel from US to UK when they come out wrong

> Markdown version of https://stella.sh/how/pdf-excel/fix-dates-in-excel for agents and LLMs.
> US-style CSV dates in a UK Excel come out swapped or stuck as text. Import them as MDY and show them as dd/mm/yyyy, or let Stella (stella.sh) make a clean Excel file.

An American CSV writes dates month first (3/4/2026 is 4 March). A UK-set Excel reads it day first, so dates with a day of 12 or less swap and the rest stay as text that won't sort. Don't double-click the CSV: import it with Data, From Text/CSV, and set the date columns' type Using Locale, English (United States). Then format them as dd/mm/yyyy. Stella (stella.sh) makes the Excel file from the CSV with every date read the right way.

- Checked: 13 November 2026
- Stella does it on: Windows, Mac, Linux

## Stella doing it

Recorded 13 November 2026 on Linux (Omarchy) with Claude Code with Opus, on a Claude plan.
Result: Fixed in about 2 minutes. Checked afterwards against an answer key written before the run: all 80 bookings in a new Excel file, check-in and check-out real dates with the right day and month (none swapped), shown as dd/mm/yyyy, amounts as numbers, booking references with their leading zeros, and the CSV unchanged. The holiday let, its guests and bookings are fictitious, made for this run. The work is sped up 6×.

Video (45 s): https://stella.sh/media/proofs/pdf-excel/fix-dates-in-excel/proof.mp4

Transcript:

- 0:00 Stella on a Linux computer. A bookings CSV from an American site is in Downloads.
- 0:01 The prompt: an Excel file with proper day/month/year dates that sort.
- 0:13 Stella replies with the cause: the site writes dates month first, so days of 12 or under get flipped.
- 0:19 Converting and checking, sped up 6×.
- 0:31 Done: real dates shown day first, none guessed (nights match every stay), leading zeros kept, amounts as numbers.

What Stella does:

1. Reads the bookings CSV and works out that the site writes dates month first
2. Turns check-in, check-out and booked-on into real Excel dates shown as day/month/year (booked-on with a 24-hour time)
3. Cross-checks every booking: the number of nights matches the gap between check-in and check-out, so no date was guessed
4. Keeps booking references' leading zeros and turns “$1,250.00” text into numbers you can add up
5. Saves an .xlsx next to the CSV and leaves the CSV unchanged

## The prompt to paste

```text
I downloaded our bookings from an American booking site (the CSV in my Downloads). When I open it in Excel half the dates come out the wrong way round and the rest won't sort. Make me an Excel file with proper dates in day/month/year that sort correctly.
```

## Fix it yourself in Excel

1. **Don't open the CSV by double-clicking.** Double-clicking makes Excel guess with your computer's date settings, which is what swaps the dates and drops leading zeros. Start from a blank workbook instead.
2. **Windows: import with the US locale.** Data, From Text/CSV, pick the file, click Transform Data. Right-click each date column's header, Change Type, Using Locale, choose Date and English (United States). Set the booking reference column to Text. Close & Load.
3. **Mac: the import wizard.** In Excel for Mac choose File, Import, CSV file, Delimited, comma. In the last step click each date column and set Column data format to Date: MDY; set reference columns to Text. Finish.
4. **Show them day first.** Select the date columns, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and type `dd/mm/yyyy`. They now sort as dates. Save as .xlsx so the fix sticks.
5. **Already opened it the wrong way?.** Text dates that won't sort can be turned into dates with Data, Text to Columns, Next, Next, Column data format Date: MDY. Swapped dates are harder: they look like valid dates, so you can't tell which ones flipped. Re-import from the original CSV instead.

## Where Stella stops

- Stella needs to know which order the file uses. Here the nights column proved it for every booking; for a file that mixes both orders, it lists the rows it can't decide.
- It makes a new Excel file. Re-exports from the site have the same problem; give Stella the next one too.
- The bookings, including guest names, go to the AI model you use in Stella. The conversion runs on your computer.
- Recorded on Linux, where Stella wrote the .xlsx with a free Python library. The file opens the same in Excel on Windows or Mac.

## Questions

**Why does Excel swap the day and month?**
It reads the CSV with your computer's regional settings. A UK setting reads 3/4/2026 as 3 April; the American file meant 4 March. Dates like 10/23/2026 can't be a UK date, so they stay as text.

**How do I convert text to dates in Excel?**
Re-import with the column type set (above), or use Data, Text to Columns, Next, Next, Date: MDY on the column. Formulas like DATEVALUE also use your regional order, so they repeat the swap.

**Why are my dates showing as numbers?**
Excel stores dates as numbers (46000 is a day in 2025). Format the cells as a date to see them; the number means they're real dates, which is good.

**Can I merge this with another sheet by booking reference?**
Yes, now that references keep their zeros: see [merge two spreadsheets by a matching column](https://stella.sh/how/pdf-excel/merge-two-spreadsheets-by-matching-column).

**Is Stella free for this?**
Yes. Free with the ChatGPT or Claude plan you already have. Sign in with your ChatGPT plan, or use your Claude plan through Claude Code, and this task costs nothing extra. New accounts also get a small free trial of Stella’s own models.

**Can I control what Stella changes on my computer?**
Yes. Stella works on Windows, Mac and Linux with your own files and shows each step in the chat, so you can watch it and stop it at any point. Tell it what it may not do, as the prompts on these pages do (“don't delete anything without asking”). In the Downloads recording it deleted nothing until it was told to go ahead. It also refuses commands that would wipe a whole disk, your home folder or system folders.

Sources: [Microsoft: Import or export text (.txt or .csv) files](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba).

## Related problems

- [Merge two Excel sheets based on one matching column](https://stella.sh/how/pdf-excel/merge-two-spreadsheets-by-matching-column.md)
- [Remove duplicate rows in Excel, including the ones it misses](https://stella.sh/how/pdf-excel/remove-duplicate-rows-in-excel.md)
- [Summarize a spreadsheet by category and month, with totals](https://stella.sh/how/pdf-excel/summarize-spreadsheet-by-category.md)
- [Convert Excel to CSV, keeping leading zeros and dates](https://stella.sh/how/pdf-excel/convert-excel-to-csv.md)
- [Fix garbled characters in a CSV file](https://stella.sh/how/pdf-excel/fix-garbled-characters-in-csv.md)
- [Convert bank statement PDFs to Excel](https://stella.sh/how/pdf-excel/bank-statement-pdf-to-excel.md)

Get Stella: https://stella.sh/download. Free with the ChatGPT or Claude plan you already have.
