Calling all Excel experts!

[gnomepunk]

This is a good one. I'm using Excel 2003.

Two ladies in my office have a bunch of older excel files. The problem is when you copy a date from one workbook to another the date gets 3 years and 1 day subtracted from it.

Example:
11/21/2005 becomes 11/20/2001

Excel stores date in a serial format with 38677 representing "11/21/2005". Format an empty cell as a date and enter "38677", you will see today date. Press [CTRL]+[~] to switch to formula view and you will see 38677. Press [CTRL]+[~] again to switch back.

In the broken workbook the serial format is 37215 -> 11/21/2005. So if you enter 37215 you will get 11/21/2005, but in a new workbook (or a non-broken one) you will get 11/20/2001.

Everything works fine in the broken workbook until you need to copy something out of it that contains dates into another workbook. The only thing I can find is that Mac serial date format begins at 1904 where as Microsoft began at 1900. Since it's >2000 now, it converts 2 digit dates based on the 29/30 rule. Anything over 30 will be 19xx, anything 29 and under will be 20xx. Anyway, if I put "1" into a date cell, in the broken workbook I get 01/02/1904, in a correct workbook I get 01/01/1900. That's a difference of 1462.

Does anyone have a clue what is going on and how I can fix it? Manually fixing the workbooks will take weeks.

 

[gnomepunk]

nevermind I found it. these files must have been created in Excel for Mac.

Thanks MSDN!
How to Transfer Files Between Microsoft Excel for the Macintosh and Microsoft Excel for Windows
By default, Excel for the Macintosh uses the 1904 date system and Excel for Windows uses the 1900 date system. This means that when you type the serial number 1 in Excel for the Macintosh and format it as a date, Excel displays it as 1/2/1904 12:00 a.m. Excel for Windows displays the serial number 1 as 1/1/1900 12:00 a.m.

If you transfer files from Excel for the Macintosh to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date.

To change to the 1904 date system in Excel for Windows, follow these steps:
1. On the Tools menu, click Options.
2. Click the Calculation tab, and then select the 1904 date system check box.
To change to the 1900 date system in Excel for the Macintosh, follow these steps:
1. On the Tools menu, click Preferences.
2. Click the Calculation tab, and then clear the 1904 date system check box.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q214094