Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
If you want a little more color on the 1904/1900 date system, check out this article joelonsoftware.com/items/2008/02/19.html, about halfway down –  dsolimano Sep 22 '10 at 19:27

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
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 '11 at 1:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.