Share this content
Tags:

When Excel Dates Mysteriously Shift by 4 Years

Sep 4th 2014
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

If you've attended any of my free High Impact Excel webinars then you've probably heard me say that Excel is fraught with nuances. Sometimes the most mundane tasks, such as copying and pasting a series of dates from one workbook to another, can send you down a mysterious rabbit hole where things turn both curious and frustrating. In this article I'll explain why sometimes Excel dates may mysteriously change by 4 years, and to be more specific, 4 years and 1 day.

Microsoft Excel uses a serial number convention for tracking dates and times. On the Windows-based versions of Excel, dates are determined by the number of days that have elapsed since January 1, 1900. Thus, if we choose a date, such as July 4, 2026, then our nation's sestercentennial will fall 46,207 days after January 1, 1900. To determine this, enter the date 7/4/2026 in a worksheet cell, and then change the number format for that worksheet cell to General , as illustrated in Figure 1:

  • Keyboard shortcut: Ctrl-Shift-~ in either Windows or Mac. Cmd-Shift-~ won't work on the Mac.
  • Ribbon interface: In Excel 2007 and later you can choose General from the dropdown list at the top of the Number section on the Home tab.
  • Format Cells dialog box: Press Ctrl-1 in any version of Excel to display this dialog box, or choose Format, and then Cells in Excel 2003 and earlier. Click on the Number tab and then choose General.

Figure 1: In the Windows version of Excel, dates are based on the number of days since January 1, 1900.

This is all well and good, and explains how we can determine the number of days between two dates, as for Excel it's a simple arithmetic operation. If only this discussion could stop there.

Excel for Mac 2008 and earlier uses a different convention for tracking dates, which is tracking dates as the number of days from January 1, 1904. By default, Excel for Mac 2011 uses the same 1900 date system as the Windows versions do.

Here's where things get tricky. If you create a new workbook on either the Mac or Windows platforms that have the 1904 date system turned on, then changing the number format for 7/26/2026 to General will reveal 44,745, or the number of days since January 1, 1904. Figure 2 illustrates where to toggle the 1904 date system on and off.

Figure 2: Mac 2008 and earlier uses the 1904 date system. This causes confusion when copied into other workbooks that are based on the 1900 date system.

The 1904 date system is workbook specific, so let's say that you create a workbook in Excel for Mac 2008, and then email that spreadsheet to the office to open in Excel for Windows. In this case you won't have any issue, as the 1904 date system setting remains enabled in Windows for that workbook unless you turn it off. So, such workbooks can travel back and forth between platforms without issue, as illustrated in figure 3. The issue will arise when you copy one or more dates from a 1904 date system enabled workbook into a 1900 date system workbook. At that point you'll see all the dates shift by 4 years and a day, as illustrated in figure 4.

Figure 3: Workbooks with the 1904 date system enabled can be used between platforms without issue.

Figure 4: Issues arise when you copy from dates from a 1904 enabled workbook into a 1900 date system workbook.

If there are no other dates in the workbook you're copying into, the simple fix is illustrated in figure 5:

  • Excel 2007 and later: Choose File (or the Office button in Excel 2007), Options, and then Advanced. Scroll down to the When Calculating This Workbook section and turn on the Use 1904 Date System option.
  • Excel 2003 and earlier: Choose Tools, Options, and then on the Calculation tab turn on Use 1904 Date System in the Workbook Options section.
  • Excel for Mac 2011: Choose Excel, Preferences, and then on the Calculation turn on the Use 1904 Date System option.
  • Excel for Mac 2008: If you work between Windows and the Mac platform, in this version choose Excel, Preferences, and then on the Calculation turn off the Use 1904 Date System option so that you won't inadvertently end up comprising dates that you copy from one workbook to another.

Figure 5: Follow these steps to turn on the 1904 date system so the dates return to their original day and year.

Now keep in mind that you do not want to turn this option on if you have existing dates in the workbook, because then those dates will roll forward by 4 years and a day. In that case, copy your dates from the 1904 date system-originating workbook into a new, blank Excel workbook. Turn the 1904 Date System on for that workbook, and then copy the dates from there into your Windows-originating workbook that already has other dates.

Keep in mind that the 1904 Date System option is controlled on a workbook-by-workbook basis in all versions of Windows and Excel for Mac 2011 and later, which means you must turn the 1904 date system on when you wish to use it. Conversely, in Mac 2008 and earlier you must manually turn the 1904 date system off if you want easy compatibility with the Excel for Mac 2011 or the Windows versions of Excel.

Tags:

Replies (2)

Please login or register to join the discussion.

avatar
By Gretchen Zimmermann
Jun 26th 2015 01:12

Thank you for this article! You just saved me a ton of time! I have an old Mac and a Windows laptop and the pasted-in dates had me flummoxed!

Thanks (0)
avatar
By tiptoptessie
Aug 18th 2017 09:45

Nothing better that randomly searching an issue on google and coming across a heading that describes it exactly!

Thank you!!

Thanks (0)