Strange behavior

[Oscar A. Moreno]

I'm having trouble with spreadsheets downloaded from the internet. It
appears that Excel makes changes to DATE-TIME data in spreadsheets. It
shifts the dates somehow. Has anyone seen this behavior? Does anyone have
a resolution? I have visited the Mactopia website with no answers on this
problem.

TIA

 

[JE McGimpsey]

I'm having trouble with spreadsheets downloaded from the internet. It
appears that Excel makes changes to DATE-TIME data in spreadsheets. It
shifts the dates somehow. Has anyone seen this behavior? Does anyone have
a resolution? I have visited the Mactopia website with no answers on this
problem.

XL has two date systems, the WinXL default 1900 Date system (where 0 =
31 December 1899 00:00:00) and the MacXL default 1904 Date system (where
0 = 1 January 1904 00:00:00). The setting is a workbook-level setting.

The date system that XL uses to display dates is determined by the first
workbook it opens. So if you open a 1904-based workbook, and
subsequently open an 1900-based workbook, your dates will be 4 years and
1 day in the future (the one day because the 1900 system includes a day
that never existed: 29 February 1900).

If you were to close XL and open the file by double-clicking, the dates
would read correctly.

Both MacXL and WinXL work consistently within each system, so if you
send a 1904-based workbook to a WinXL user, it will open fine unless the
user opens it in an instance that already has a 1900-based workbook.

If you wish to convert from one to the other, a simple way is to enter
the number 1462 (the number of days in 4 years and a day) in an empty
cell. Copy the cell. Select your dates and choose Edit/Paste Special,
selecting the Add (1904->1900) or Subtract (1904->1900) radio buttons.

 

[Bernard Rey]

I'm having trouble with spreadsheets downloaded from the internet. It
appears that Excel makes changes to DATE-TIME data in spreadsheets. It
shifts the dates somehow. Has anyone seen this behavior? Does anyone have
a resolution? I have visited the Mactopia website with no answers on this
problem.

I guess these are set to the Windows date system. Which is four years and
one days "older" than the Mac date system. See more about it here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;180162

There are different ways to workaround this. The simple one is to copy
"1462" in on cell and the do a "Paste Special" checking both the "Values"
and the "Substract" buttons.

If you do this on a regular basis (i.e. exchanging sheets with Windows
users, you can download an XLA add-in that can do the thing for you:
http://tinyurl.com/bxn59

 

[Paul Berkowitz]

The date system that XL uses to display dates is determined by the first
workbook it opens. So if you open a 1904-based workbook, and
subsequently open an 1900-based workbook, your dates will be 4 years and
1 day in the future (the one day because the 1900 system includes a day
that never existed: 29 February 1900).

Hmmm. How about the fact that, as you say, in the 1900 Date system 0 =
31 December 1899 00:00:00 and in and the MacXL default 1904 Date system
0 = 1 January 1904 00:00:00. Therefore the first day in the Mac system is 4
years and a day later than the Win system. If it's true that only the Win
system, and not the Mac system, has a Feb 29, 1900, then the difference
would become 4 years and 2 days for every date after Feb 28, 1900, and thus
for the whole date system covered by the Mac XL. So maybe in 1900 Date
system 0 = 1 January 1900 as Bernard's KB article says? (Or just possibly 31
Dec 1899 23:59:59?) I don't have Windows available at the moment to check.

Or what's wrong with my logic here?

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[JE McGimpsey]

Or what's wrong with my logic here?

Nothing's wrong with your logic. I didn't add that WinXL doesn't
consider a value a date until it's >1.

So where the value 0.5 in MacXL default 1904 date system displays as

01 January 1904 12:00:00

in WinXL it displays as

00 Jan 1900 12:00:00

 

[Bernard Rey]

Hmmm. How about the fact that, as you say, in the 1900 Date system 0 =
31 December 1899 00:00:00 and in and the MacXL default 1904 Date system
0 = 1 January 1904 00:00:00. Therefore the first day in the Mac system is 4
years and a day later than the Win system. If it's true that only the Win
system, and not the Mac system, has a Feb 29, 1900, then the difference
would become 4 years and 2 days for every date after Feb 28, 1900, and thus
for the whole date system covered by the Mac XL. So maybe in 1900 Date
system 0 = 1 January 1900 as Bernard's KB article says? (Or just possibly 31
Dec 1899 23:59:59?) I don't have Windows available at the moment to check.

Or what's wrong with my logic here?

There is no "logic" here. It's an old story. The Windows version of Excel
was designed to be compatible with Lotus 1-2-3. And so included the bugs of
the Lotus product (yes, in those days it was more important to keep
compatibility with it's competitors than with the already existing Mac
version...) ! There has never been a Feb 29, 1900 (and I guess it is to
avoid that very issue that the Mac date systems starts in 1904). And there
has never been a January 00, as indicated by JE.

More about this issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;214326

 

[Oscar A. Moreno]

Thanks a lot for the enlightening comments from everyone. What the
conundrum.
Thanks again!!!