Date columns in a CSV: serials, swaps, formats

A date column can look right on screen and still be wrong in the saved file. This post covers three date problems. Each section follows the same order: problem, cause, find, fix, recheck. Every step below is written from known spreadsheet behavior and was not run on a real file. Steps marked (verify) need a quick test on your own data.

Before you start, save a copy of the original file and work on the copy. Below, "column A" means your date column wherever it is: insert two empty columns right after it and use them as B and C.

1. Five-digit numbers instead of dates (for example 45992). Problem: the date column shows numbers like 45992. Cause: spreadsheets store a date as a day count. A cell formatted as General or Number shows that count. Find: click a cell. If the formula bar shows a 5-digit number, it is most likely a date stored as a day count. (Not run.)

Use three columns, and do the steps in this order. Column A is the source: it holds the date numbers; do not edit it. Column B is a helper: it holds a formula result and is not the final output. Column C is the target: it holds the yyyy-mm-dd text that goes into your CSV.

Fix: 1. Leave column A as it is. Do not change its values or format. (Not run.) 2. In B2 put =TEXT(A2,"yyyy-mm-dd") and fill down. In some spreadsheet programs the format code can depend on the language setting (verify). (Not run.) 3. If the numbers in A are stored as text, use =TEXT(VALUE(A2),"yyyy-mm-dd") in B2 instead (verify). (Not run.) 4. Copy column B and paste as values only into column C. C is now the target. (Not run.) 5. Only after the recheck below passes, copy the header from A1 into C1, then delete columns A and B so C sits where the date column was. (Not run.)

Recheck: in C, confirm every value has the same yyyy-mm-dd pattern. Save as CSV, reopen it as plain text and confirm the dates read as intended. (Not run.) Note: yyyy-mm-dd is a common choice, but check the store's template for the date format it wants before converting. (Not run.)

2. Day and month swapped. Problem: 03/04/2026 is 4 March for one reader and 3 April for another. Cause: the region or locale setting decides how such text is read (depending on the program, the computer's region setting or a locale option in the file's settings (verify)). The same text can read differently under a different setting. Find: look at values whose first number is above 12, such as 25/03/2026. Such a value cannot be a month, so it shows which order the source likely uses. A value like 03/04/2026 cannot show its own order. (Not run.)

Fix: 1. Decide the intended order from the original source, not from the file. (Not run.) 2. Open the file again so the date column is read as text, not converted. Use your spreadsheet program's text import steps and set that column to Text (verify the menu name in your version). If the import has an option that converts text to numbers and dates, turn it off (verify the option name). (Not run.) 3. Set the locale to match the source (a locale option in the file's settings or the computer's region setting, depending on your program; verify), then write final dates with the steps in section 3. (Not run.) Recheck: pick three dates with a day above 12 and three with a day of 12 or below. Confirm each matches the source. (Not run.)

3. Mixed date formats in one column. Problem: one column holds 2026-03-04, 4 Mar 2026 and 03/04/2026. Cause: some cells are real dates and others are text that only looks like a date. Set up three columns. Column A: source dates (leave as they are). Column B: helper, converted dates. B2 =IF(ISNUMBER(A2),A2,DATEVALUE(A2)), fill down. This keeps cells that are already real dates and converts text dates (verify). (Not run.) Column C: target, formatted text output. C2 =TEXT(B2,"yyyy-mm-dd"), fill down. (Not run.)

Notes: DATEVALUE reads text only if your region can read it (verify). A #VALUE! in B means that cell was not understood; retype it by hand. A value like 03/04/2026 converts without an error but may use the wrong day/month order; check such values with section 2. (Not run.)

Recheck B: in a spare column, =ISNUMBER(B2) must be TRUE on every row. Fix any FALSE first. (Not run.) Recheck C: C is text, so ISNUMBER is not expected to be TRUE there. Instead, =LEN(C2) must be 10 on every row, and every value must follow the same yyyy-mm-dd pattern (check by eye or with a filter). (Not run.) Finish: confirm the store's template accepts the yyyy-mm-dd format first. Then copy C, paste as values only, copy the header from A1 into C1, and remove A and B so C sits where the date column was. (Not run.)

Run the free CSV check. Before or after fixing, run the file through the free CSV check. It checks only the first 20 data rows, in your browser; the file is not uploaded. It treats a column as a date column only when the header names a date (for example Date, Release date, created_at, Tarih or Fecha). In such a column it can warn about three things: 5-digit numbers from 20000 to 60000 (day counts, not dates), day and month that could be swapped when no checked row has a part above 12, and more than one date format in the column. The report names the row and the column, never the cell value. It skips values it cannot read as a date, such as 4 Mar 2026, so a clean result does not prove the column is right. This post does not quote any warning text. The check does not decide which order your dates use; that stays with you and your source.