pixel
Select Page

Why does the date change when you copy cells from an old Excel spreadsheet to a new one?

I had a customer with an Excel spreadsheet saved in the Excel 97 format. They opened it up in Excel 2007 and copied the contents to a new spreadsheet – and the date within the date cell changed by four years!

This anomaly has to do with Excel Date Codes.

 


 

 
There’s a little known feature of Excel to format dates two ways:

  • 1900 Date System
  • 1904 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904.

Here’s the problem

If you bring a spreadsheet that was formatted in a version of Excel where the 1900 Date System was used, and put that data in an Excel spreadsheet using the 1904 Date System then the Excel date code is interpreted differently.

For example: July 5, 1998 will show as 35981 in the 1900 Date System and 34519 in the 1904 Date System.

 

How to change the Excel Date System in Excel 2007 and 2010

  • – Open or switch to the workbook
  • – Click the File menu, select Options
  • – Click Advanced and scroll down to the When Calculating this Workbook
  • – Check or uncheck the Using 1904 Date System option

Here’s an article that shows you how to correct these shifted dates in the new spreadsheet if you need to.

 
 
[Sources]