I have an Excel 2002 workbook with records from the past ten years. When I copy a sheet from this workbook to a new workbook, the dates all back up four years. For example, 25-Sep-04 becomes 25-Sep-00. I have installed all available updates with no difference in the results. I started this worksheet in some antique version of Excel, converted it to Excel 97, and then converted to Excel 2002 after it was recovered from a crashed hard drive.
Excel stores any date as a whole number of days since an arbitrary starting date. The default first date, represented by the number 1, is January 1, 1900. However, Excel also supports an alternate date system in which the default first date is January 2, 1904. This 1904 date system is the default for the Macintosh version of Excel. Somewhere in the dim and distant past, you selected that date system for the workbook that's giving you trouble. When you copy a worksheet from it into another workbook that uses the default date system, Excel interprets the dates differently, and this produces the four-year problem you've noticed. (Actually, it's a four-year-and-a-day error25-Sep-04 becomes 24-Sep-00).
The best way to correct this is to change the antique workbook so it uses the default date system. To do that, go to Tools | Options | Calculation and deselect 1904 date system. All the dates in the spreadsheet will change to the wrong date. In a blank cell, enter the number 1462. This is the number of days between 1/1/1900 and 1/2/1904. Copy this cell. Now select all the cells with the wrong dates and select Edit | Paste Special | Add. The numbers are now correct, but they're serial numbers instead of dates. That's easily fixed by going into Format | Cells and choosing the appropriate date format.
If you would rather not deal with converting serial numbers, instead of entering 1462 as an integer enter it as a date. You can do this by typing either 1/1/1904' or =DATE(1904,1,1). When you then use Paste Special, the dates will stay dates because Paste Special also pastes the format of the source cell.
Once your workbook is changed to the default date system, you won't run into any of those four-year time warps any more.
More Excel Solutions: