Skip to content

How-to

How to Fix Excel Dates That Are Four Years and One Day Off

Dates copied between workbooks moved by 4 years and 1 day? That is the 1900 vs 1904 date system. Find which one each workbook uses and shift the dates back.

The problem

“I copied dates into another workbook and every one changed to the day before, four years earlier.”

Why it happens

Excel stores a date as a serial number of days, and it has two ways to count them:

  • the 1900 date system, the default in Excel for Windows and in new workbooks on every platform today, where day 1 is 1 January 1900;
  • the 1904 date system, the old default of Excel for Mac, where day 0 is 1 January 1904.

Each workbook keeps the system it was created with. When a date moves between two workbooks as a number (copy and paste, a linked cell, a Power Query step, a script), it keeps its serial but changes its meaning. The difference is always 1462 days: four years and one day. Copy from a 1904 workbook into a 1900 one and dates move four years and one day earlier; copy the other way and they move four years and one day later.

Because every date shifts by the same amount, the result looks plausible, and the error is often noticed months later.

Fix it on Date And Time Converter

  1. Take one shifted value and its correct date, if you know it. In Excel, show the shifted cell as a number (Format Cells › General) to get its serial.
  2. Type that serial into the converter and choose Excel serial (1904 date system), then Excel serial (1900 date system). The reading that gives the right date tells you which system the number was written in; the example shows the two readings of one serial.
  3. For a CSV column of serials, open it in the file workspace, choose the serial format and set the column’s date system to the one the source used. The result is written as real dates, which no longer depend on either system.
Worked example: Serial 44010 in a workbook that uses the 1904 date system.
Input
44010 read as Excel serial (1904 date system)
Result
2024-06-29 YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion

Open in the converter: 44010 → 2024-06-29

Worked example: The same number in a 1900-system workbook: four years and one day earlier.
Input
44010 read as Excel serial (1900 date system)
Result
2020-06-28 YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion

Open in the converter: 44010 → 2020-06-28

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

Shift the copied dates back

  1. Type 1462 in an empty cell and copy it.
  2. Select the shifted dates.
  3. Choose Paste Special, then under Operation choose Add if the dates are four years too early, or Subtract if they are four years too late.
  4. Delete the 1462 cell. Check one date against its source.

Find and change a workbook’s date system

In Excel for Windows: File › Options › Advanced, section When calculating this workbook, box Use 1904 date system. On a Mac: Excel › Preferences › Calculation (Settings instead of Preferences in recent versions). Changing the setting does not convert the dates already in the workbook: every existing date moves by four years and one day. Change it only in a new or empty workbook, or shift the dates as above afterwards.

Avoid it next time

  • Make both workbooks use the same system before copying.
  • Copy dates as text in an unambiguous format (YYYY-MM-DD), not as numbers.
  • In Power Query and scripts, read date columns as dates, not as their serials.

Pitfalls

  • Fixing a column twice. Paste Special › Add 1462 applied twice puts the dates four years too late. Keep a copy before you shift.
  • Mixed columns. If only some rows were pasted from the other workbook, only those rows are shifted. Compare against the source rather than shifting the whole column.
  • Times survive. The shift is a whole number of days, so the time of day in each cell is unchanged; only the date moves.

Questions

Which workbooks use the 1904 date system?

Workbooks first created in Excel 2008 for Mac or earlier used it by default; Excel for Mac 2011 and later use 1900. Newer Excel uses 1900 for new workbooks, but every workbook keeps the system it was created with, so old Mac files still carry 1904.

Why is the difference 1462 days?

From 1 January 1900 to 1 January 1904 is 1460 days. The 1900 system also counts a 29 February 1900 that never existed, and it starts at day 1 where the 1904 system starts at day 0: 1460 + 1 + 1 = 1462.

Does Google Sheets have a 1904 system?

No. Sheets counts days from 30 December 1899, so a serial from a 1904 workbook pasted into Sheets as a number shows the same four-year shift.