Strange behavior

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oscar A. Moreno - 26 Apr 2005 16:07 GMT

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

Reply to this Message

JE McGimpsey - 26 Apr 2005 17:32 GMT

> 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.

Reply to this Message

Paul Berkowitz - 26 Apr 2005 18:28 GMT

On 4/26/05 9:32 AM, in article
jemcgimpsey-1AC971.10325026042005@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

> 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?

Signature

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.

Reply to this Message

JE McGimpsey - 26 Apr 2005 18:45 GMT

> 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

Reply to this Message

Bernard Rey - 26 Apr 2005 20:25 GMT

>> 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
[quoted text clipped - 13 lines]
>
> 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

Signature

Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

Reply to this Message

Bernard Rey - 26 Apr 2005 17:40 GMT

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

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

Signature

Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr

Reply to this Message

Oscar A. Moreno - 27 Apr 2005 16:09 GMT

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

On 4/26/05 11:07, in article BE93D109.162F0%oscar.moreno@att.net, "Oscar A.
Moreno" <oscar.moreno@att.net> wrote:

> I'm having trouble with spreadsheets downloaded from the internet. It
> appears that Excel makes changes to DATE-TIME data in spreadsheets. It
[quoted text clipped - 3 lines]
>
> TIA

Reply to this Message

Mac Forum / Applications / Excel / April 2005

Strange behavior