Any idea why when I paste a date (format date 03/14/01) into a blank
excel document, the date changes. For example, 09/26/05 becomes
09/27/09 in the new document, although the cell format is the same in
both documents (format date 03/14/01)?
The workbooks have different base dates. The one you're copying from is
set to the WinXL default 1900 date system (base date 31 December 1899)
while the one you're copying to has the MacXL default 1904 date system
(base date 1/1/1904). Since XL stores dates as integer offsets from the
base date, you're seeing a 4 year + 1 day shift (the 1 day is an error
in the 1900 system which includes a phantom 29 February 1900).
You can change the date system of your destination workbook (date
systems are workbook specific) choosing Preferences/Calculation and
unchecking the 1904 date system checkbox.
As an alternative, you can enter 1462 in a cell, copy the cell, then
select the cells with the advanced dates. Choose Edit/Paste Special,
selecting the Values and Subtract radio buttons. Click OK.
