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.

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)
avatar
By derbyp
Nov 14th 2017 11:31

Hi there,
Sorry I know this is an old thread.
The quick and easy solution I found (at least for Excel 2010 and later) is to select "Match Destination Formatting" when you paste into the new sheet. It transposes the dates to the correct format automatically. To do this, right-click on the cell you are pasting to, you will see "paste options", the third one is the one you want.
For me, copying to a blank workbook as originally suggested duplicates the issue and still leaves you unable to copy correctly to the recipient sheet.
Many thanks to David for posting this, I had no idea the problem existed until now, my 1904 sheet came from a csv downloaded from a bank. This helped me make sense of the senseless...

Thanks (0)