MAC vs Windows date system

[DebbieM]

I recently switched to a MAC and trying to use spreadsheets created on
a PC. The problem I'm having relates to the 1904 date systems used by
MAC and the 1900 used by Windows - all my dates are off by 4 years so I
can't create pivot tables because the dates are "invalid" since
12/16/05 is recognized as 12/16/09. Can this be easily corrected? I
can substract 1461 (number of days in 4 years) from each date but will
this cause any errrors?
Debbie

 

[JE McGimpsey]

I recently switched to a MAC and trying to use spreadsheets created on
a PC. The problem I'm having relates to the 1904 date systems used by
MAC and the 1900 used by Windows - all my dates are off by 4 years so I
can't create pivot tables because the dates are "invalid" since
12/16/05 is recognized as 12/16/09. Can this be easily corrected? I
can substract 1461 (number of days in 4 years) from each date but will
this cause any errrors?

Yes, but not for any reason you're expecting...

XL will adopt the date system of the first worksheet that's opened
during a session. The problem occurs when you have one of each, as
you've found.

To "correct" the dates, you have to add or subtract *1462*, not 1461.
The reason is the Win/1900 date system includes 2/29/1900, a date that
never existed.

 

[Rup]

DebbieM a écrit :

I recently switched to a MAC and trying to use spreadsheets created on
a PC. The problem I'm having relates to the 1904 date systems used by
MAC and the 1900 used by Windows - all my dates are off by 4 years so I
can't create pivot tables because the dates are "invalid" since
12/16/05 is recognized as 12/16/09. Can this be easily corrected? I
can substract 1461 (number of days in 4 years) from each date but will
this cause any errrors?
Debbie

Debbie,
There is a per-worksheet option in Preferences/Compute then "Worksheet
Options" (bottom-right area), "Calendar since 1904" option. Menu titles
might be slightly different, I am translating from French.

Rup