How can I fix a date that changes by 4 years and 1 day when pasted between Excel workbooks

Question

In Excel dates are represented internally by a floating point number where the integer part is the number of days since "some date" and the fractional part is how far into that day (hence the time). You can see this if you change the format of a date - like 4/10/2009 to a number 39905.

But when pasting a date between two different workbooks the date shifts by 4 years and one day!!!

In other words "some date" is different between the two workbooks.

In one workbook the number 0.0 represents 1/0/1900 and in the other 0.0 represents 1/1/1904.

Where is this set and is it controllable? Or does this represent a corrupted file?

These workbooks where originally from Excel 2000 but have been worked on now in Excel 2007 and Excel 2003.

I can demonstrate the problem between the two workbook files in both 2003 and 2010. The exact history of when they were created or what versions of Excel have been used on each is unknown.

Answer 1

In 2003, go to the Tools Menus, then choose Options, then choose the Calculation tab.

Select/Unselect the box to change the date setting.