dates off by four years and a day

[Eric_Nelson]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

On the Mac, I read a text file containing dates of the form 2007-09-01 into a new spreadsheet. The dates are shown correctly in the spreadsheet.

If I then copy the page into another spreadsheet that happens to have been created on windows, all of the dates are off by four years and a day. In the example above the date is 2003-08-31.

I've checked and the date numbers are the same in both sheets. This is a pretty nasty problem... any ideas?

Thanks,
Eric

 

[Eric_Nelson]

I should point out that the same exercise carried out on windows results in the correct dates displayed.

 

[CyberTaz]

It's not a "pretty nasty problem" if you don't copy/paste between files that
use different Date Systems Dates are stored as sequential numbers in any
value-based application & the date system used determines what the
Zero-Point is for the date values in the files it creates.

You'll find the full explanation & suggestions here as well as in several
thousand other locations around the web - Google is your friend :

http://support.microsoft.com/kb/180162

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Eric_Nelson]

Thanks Bob.

 

[Scoobyduder]

Sorry to disagree with you, CyberTaz, but different date systems between the Mac and Windows versions of Excel is a "pretty nasty problem" and one that could have been solved in Office 2008 by making the 1900 date system the default. The user should not have to set this preference for EVERY spreadsheet he or she creates. And it further complicates things when spreadsheets are shared with Windows users. This is one of many, many examples of how incompatible Excel 2008 for Mac and Excel 2007 for Windows really are.

 

[CyberTaz]

Well, you're certainly welcome to disagree with anyone on anything but if
you're going to do so please get the facts straight first

The fact that PCs & Macs use 2 different default date systems is no source
of incompatibility whatsoever. The date system is workbook-specific, so
regardless of which version of Excel creates the file the dates will be
correct when that file is opened and edited in any other version. Most users
are never even aware that different date systems exist and have no idea
which is employed by the files they work with daily.

Further to that, it isn't a Mac/PC thing. There are any number of reasons
why a PC user might choose to use the 1904 date system in any given file or
as their default. Another PC user will have the same "pretty nasty problem"
if they *copy & paste* from that file into one that is set for 1900 or vice
versa. Again, as long as the user *opens* the file, which date system is in
use is totally transparent. Not to mention that Mac isn't the only OS that
is based on 1904. In fact, I wouldn't be surprised if Windows (which evolved
from DOS) is about the only "contemporary" OS that uses 1900 as the base
date system. Just a little coincident history...

Were you aware that Excel originated on the Mac & was not introduced to the
PC until several years later? Therefore, the original date system used by
Excel *is* the 1904 system. IOW, it is hardly anything new or unique to
Excel 2008. Apps on the Mac OS have *always* used the 1904 date system.

I don't claim this to be a fact, but my understanding is that the use of
1900 was dictated when Excel was ported to the PC because of restrictions of
the DOS operating system & that the older (1900) system was the
"established" standard as a result. If anything it could better be argued
that Windows should get up-to-date by adopting the newer system... But that
isn't going to happen because of the havoc that *would* be caused among the
existing user base. The same is true of the Mac OS, though... It isn't about
to switch horses on its established users, either.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Sorry to disagree with you, CyberTaz, but different date systems between the
Mac and Windows versions of Excel is a "pretty nasty problem" and one that
could have been solved in Office 2008 by making the 1900 date system the
default. The user should not have to set this preference for EVERY
spreadsheet he or she creates. And it further complicates things when
spreadsheets are shared with Windows users. This is one of many, many
examples of how incompatible Excel 2008 for Mac and Excel 2007 for Windows
really are.

Or it could have been solved back in the late 1980's when XL was ported
from Mac (where it was developed from MathPlan) to DOS and then Windows.
It could then have been standardized on the existing standard - the 1904
system.

Unfortunately, because of compatibility issues with VisiCalc and 123, MS
chose to go with the 1900 system. They even included a known bug - the
fictitious "February 29, 1900", which never occurred outside the virtual
world.

But since date system settings are *workbook* specific, and work the
same way for both MacXL and WinXL, there's zero incompatibility.
Changing the default, as you suggest, would simply flip the problem, not
solve it.

There's no need to set the preference for "EVERY spreadsheet". Any user
who regularly copies dates from 1900-system workbooks to new MacXL
workbooks would undoubtedly benefit from changing their default workbook
date preference setting to that system.