Why Excel Dates Become Numbers in CSV—and How to Export Them Cleanly

If an Excel date becomes a number such as 40729 in a CSV export, the number is usually not random. It is Excel’s stored date serial shown without a date display format.

The safest publishing workflow is:

1. Keep the original workbook.
2. Create an export column containing unambiguous date text.
3. Save the required worksheet as CSV.
4. Inspect the raw CSV in a text editor before uploading it.

Stored value and display format are different

Excel stores dates as sequential numbers so it can sort them and perform date arithmetic. In the 1900 date system, for example, July 5, 2011 is serial 40729. Excel also supports a 1904 date system, in which that date is 39267. The systems differ by 1,462 days, so do not convert unexplained serials by assumption when the original workbook is available (Microsoft’s date-system documentation).

A cell’s number format controls what you see:

• General format: 40729
• Date format: 7/5/2011
• Custom date format: 2011-07-05

Changing the format changes the presentation, not the underlying serial. Microsoft describes Excel’s Date format as displaying date and time serial numbers as date values (Microsoft’s number-format reference).

CSV does not retain Excel cell formatting. It stores fields as delimited text, and Excel warns during export that worksheet features unsupported by text formats will not be saved (Microsoft’s CSV import and export guide).

Reproduce the issue with a small sample

Create this worksheet:

A	B	C
source_date	general_value	export_date
7/5/2011	=A2	=TEXT(A2,"yyyy-mm-dd")

Then:

1. Format A2 as a date.
2. Format B2 as General.
3. Leave C2 as the formula result.

Under the 1900 date system, the row should display as:

source_date	general_value	export_date
7/5/2011	40729	2011-07-05

The cells represent the same calendar date in three ways: a formatted date, its numeric serial and a text version prepared for export. Microsoft documents both the 40729 serial for July 5, 2011 and the principle that a serial requires a date format to display as a date (Microsoft’s date-system documentation; Microsoft’s DATEVALUE reference).

Prepare a stable date column for CSV

For public data, yyyy-mm-dd is usually clearer than a locale-dependent value such as 7/5/2011, which readers may interpret as July 5 or May 7.

If the real Excel date is in A2, add a helper column and use:

=TEXT(A2,"yyyy-mm-dd")

Excel’s TEXT function converts a date or time to text in a specified format (Microsoft’s date-to-text guidance). Fill the formula down, then check several records, including:

• the earliest and latest dates;
• a date with a day of 13 or higher, which exposes month/day confusion;
• blank cells;
• date-time values that may contain a time component.

If blank source cells must remain blank, use:

=IF(A2="","",TEXT(A2,"yyyy-mm-dd"))

The date-only formula discards the displayed time. If time matters to the dataset, define and export a timestamp format instead of silently reducing the value to a date.

Rename the helper column to the final field name and omit the original date and serial columns from the publication export. If you need fixed text rather than formulas, copy the helper column and use Paste Special > Values in a separate export worksheet. Do not overwrite the only copy of the workbook.

You can instead apply a custom yyyy-mm-dd number format to genuine date cells before saving. A helper column is more explicit because its result is already text, but downstream tools must then deliberately parse that field if they require a date datatype.

Save and inspect the CSV

Save the prepared worksheet with File > Save As, choosing the CSV type required by the receiving system. Excel saves only the current worksheet to the text file, so repeat the process for any other sheets you need (Microsoft’s export instructions). Keep the XLSX master for a workbook with multiple tabs or features; see CSV vs XLSX for the practical differences.

Do not use reopening in Excel as your only validation. When Excel directly opens a CSV, it uses its current default data-format settings to interpret each column, so it may transform otherwise correct text during import (Microsoft’s import guidance).

Instead, open the CSV in a plain-text editor and confirm that it contains the intended characters:

source_date
2011-07-05

Not:

source_date
40729

Finally, import the CSV into the intended publishing or analysis tool. Confirm that the column is treated consistently, sorts chronologically and contains no unexpected serials. If the raw file contains numbers, return to the original workbook and prepare the export again. If the raw file contains correct dates but Excel shows something else when reopening it, the issue is import interpretation rather than the CSV text itself.