When Excel Dates Mysteriously Shift by 4 Years

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

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.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

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)
avatar
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)