Skip to content

How-to

How to Stop Excel Changing Dates in a CSV File

Excel rewrites dates, and codes that look like dates, when it opens or saves a CSV. Import the column as text, write dates unambiguously, repair the damage.

The problem

“I opened a CSV in Excel, saved it, and half the dates changed: ISO dates came back as 3/4/2024, some days and months swapped, and a few product codes turned into dates.”

Why it happens

A CSV file is plain text: it has no idea what a date is. When you double-click one, Excel reads every cell and turns anything that looks like a date into a date, using your computer’s regional settings to decide the order of day and month. Three things then go wrong:

  • Dates are re-read in your order. On a US computer, 03/04/2024 from a European system becomes March 4. On a UK computer, an American 03/04/2024 becomes 3 April. Cells that do not fit your order, such as 13/04/2024 on a US computer, stay as text, so one column ends up half dates and half text.
  • Things that are not dates become dates. Product codes such as 07-2772 (shown as Jul-72) or 1-2 (shown as 2-Jan), part numbers and gene names like MARCH1 or SEPT2 are turned into dates, and the original text is gone from the cell.
  • Saving writes the display, not the original. When you save as CSV, Excel writes each date as it is displayed. A file that held 2024-03-04 is saved back as 3/4/2024, and the next system that reads it may take that as 3 April.

Nothing warns you, and the damage spreads: every person who opens and saves the file re-applies their own settings.

Fix it on Date And Time Converter

The safest route is never to let Excel interpret the dates. The file workspace on the home page reads the raw text of the file, not Excel’s version of it.

  1. Open the original CSV, the one Excel has not saved, in the file workspace by dropping it on the home page or choosing it.
  2. Pick the date column. The workspace proposes its format from the column’s first values and checks the day/month order against the whole column; where day and month could go either way it asks you instead of guessing.
  3. Choose an unambiguous target: YYYY-MM-DD for dates, YYYY-MM-DD HH:mm:ss or ISO 8601 for date-times. The other columns are left exactly as they were in the file.
  4. If the damage is already done, for example ISO dates that came back month-first, convert the damaged column from M/D/YYYY back to YYYY-MM-DD, as in the example.
Worked example: A date Excel saved back month-first, written again as YYYY-MM-DD.
Input
3/4/2024 read as M/D/YYYY
Result
2024-03-04 YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion

Open in the converter: 3/4/2024 → 2024-03-04

A whole column?

Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).

Open the file workspace

In Excel or Google Sheets

Import the column as text instead of opening the file

In Excel, use Data › From Text/CSV rather than double-clicking. In the preview, choose Transform Data, select the column, and set its data type to Text; Excel then keeps the characters exactly as they are. The older Text Import Wizard (Data › Get Data › Legacy Wizards › From Text, once it is switched on under File › Options › Data) does the same in its third step, where each column can be set to Text, or to Date with the order spelled out (DMY, MDY or YMD).

In Google Sheets, use File › Import, upload the file, and clear Convert text to numbers, dates, and formulas.

Keep a file Excel cannot misread

  • Write dates as YYYY-MM-DD. Every regional setting reads that as the same day.
  • Do not save a CSV from Excel if other systems read it. Keep the original, and export a fresh copy from the source system or the workspace when you need one.
  • For codes that look like dates, import those columns as Text as well. Once Excel has turned MARCH1 into a date, the original text cannot be recovered from the cell.

Pitfalls

  • Converting a file Excel has already saved. If a column mixes dates Excel re-read with dates it left as text, the two halves are in different orders. Convert from the original export when you can.
  • Opening the converted file in Excel. Excel still reformats the new YYYY-MM-DD dates for display, and saving writes them in your regional format again. Hand the converted file to the next system directly.
  • Leading zeros. Excel also strips leading zeros from codes and postcodes in the same pass. That is not a date problem. Importing the column as Text prevents it, and so does switching off Remove leading zeros and convert to a number under Automatic Data Conversion in Microsoft 365.

Questions

Can I switch off Excel’s automatic date conversion?

Not completely. Recent Microsoft 365 versions have File › Options › Data › Automatic data conversion, which can stop some conversions, such as letters and numbers that look like a date. Importing the column as Text is the dependable way.

Does saving as CSV change my dates?

Yes. Excel writes each cell as it is displayed, so a date shown as 3/4/2024 is saved as 3/4/2024 whatever the file held before. Keep the original file and write a copy.

Which date format does Excel read the same everywhere?

YYYY-MM-DD (2024-03-04) is read as the right day under any regional setting. Excel then displays it, and saves it, in your regional format, which is why the copy you save may differ.

Does the file workspace change my other columns?

No. It rewrites only the cells of the column you convert; every other cell keeps the text it had in the file.