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

Posted by lcbrevard on Super User See other posts from Super User or by lcbrevard
Published on 2010-06-16T23:51:17Z Indexed on 2010/06/16 23:53 UTC
Read the original article Hit count: 228

Filed under:
|

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.

© Super User or respective owner

Related posts about excel

Related posts about date