When Excel Dates Mysteriously Shift by 4 Years

Spreadsheets and graphs on a desk
xfgiro/istock
Share this content

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:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

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)