6

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.

1

1 Answer 1

9

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

Select/Unselect the box to change the date setting.

alt text

2
  • 1
    This lets you change the date system - shifting dates as displayed but it doesn't fix the issue of wanting to normalize multiple workbooks to the same date system while KEEPING the date as previously displayed. I would have liked an option to adjust any date formated field so that the displayed date is still the same. We finally fixed them with a macro.
    – lcbrevard
    Jan 28, 2011 at 1:22
  • 1
    I really wonder why Microsoft used the year 1904 in the first place !?
    – dvdmn
    Jul 15, 2017 at 19:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .