Date changes when opening PC made spreadsheet

[steve]

Hi,

I've experienced this rather aggravating problem when opening an Excel
spreadsheet made on a PC with Excel 2000 with my mac running OS 10.3
with Excel X. Dates in a MM/DD/YYYY format are predictably changed such
that all the DD fields are -1 day, and the YYYY is -4 years. So, a date in the
PC file that is 5/30/96 gets converted to 5/29/92 when I open the file. As
you can imagine, this can be rather tedious to fix.

Anyone know why this happens and what I can do to fix it? Thanks.

Steve

 

[Michael R Middleton]

Steve -

... and what I can do to fix it? <

Excel | Preferences | Calculation | 1904 date system

- Mike Middleton, www.usfca.edu/~middleton

 

[JE McGimpsey]

Hi,

I've experienced this rather aggravating problem when opening an Excel
spreadsheet made on a PC with Excel 2000 with my mac running OS 10.3
with Excel X. Dates in a MM/DD/YYYY format are predictably changed such
that all the DD fields are -1 day, and the YYYY is -4 years. So, a date in
the
PC file that is 5/30/96 gets converted to 5/29/92 when I open the file. As
you can imagine, this can be rather tedious to fix.

Anyone know why this happens and what I can do to fix it? Thanks.

XL takes its date system from the first workbook that opens during a
session, so if you open a workbook (including your Personal Macro
Workbook) that uses the (Mac default) 1904 date system, then open a
(Windows default) 1900 date system workbook, the dates will be off by
the difference of the base dates (4 years and 1 day, the 1 day being due
to the 1900 date system's error in having a 29 February 1900).

The solution is to choose Preferences/Calculation, and uncheck the 1904
date system checkbox when working with that workbook.