I'm shocked, shocked to learn that there is a bug in Microsoft Excel
2000!
My original problem concerned converting date/time columns in Excel
to dates and times in MapInfo. MapInfo's date type does not support
the fractional parts of days (hours, minutes, and seconds) that
Excel does, so if your Excel columns are formatted as Date instead
of as General, MapInfo truncates the fractional 'time of day'
portion. That was okay; I knew about that, so I formatted my Excel
column to General and imported them as floats, assuming I could
later convert the integer and fraction to two MapInfo columns of
Date and Time. But I kept getting a small error in my conversion and
I couldn't see why. It turns out that Excel has a Y1.9K problem you
need to know about.
Excel thinks the year 1900 is a leap year, but everyone who
responded assures me it's not, so if you naively import Excel dates
into MapInfo there are a few dates that will be off by a day.
Specifically, beware of Excel dates from 1-Jan-1900 to 29-Feb-1900.
MapInfo will interpret those as 31-Dec-1899 to 1-Mar-1900. From
1-Mar-1900 onward there are apparently no date-conversion problems
from Excel to MapInfo.
If you try to convert dates yourself (by importing dates as floats
to avoid losing the time fraction), you need to remember that
Excel's dates are 1 too high from 1-Mar-1900 onward. Excel starts
counting days from 1-Jan-1900 (day 1) and screws up on day 60 (it
didn't get far, did it?) That's 1-Mar-1900 by the Gregorian calendar
but Excel redefines reality to call it 29-Feb-1900, so by numeric
count of days, Excel is forever off by one from then on --the
classic Programmer's Curse and the most common cause of the root of
all evil in software.
Taking this into account, the accepted rule for number of days over
an interval is that there are 365 days per year, except for years
which are evenly divisible by 4 for which there are 366 days. But
the years that are century markers (e.g. 1800, 1900, etc.) have only
365 days UNLESS they are also divisible evenly by 400. Thus, the
year 2000 IS a leap year, on which we spent millions during the Y2K
fiasco trying to ensure that the world as we knew it would last long
enough to enjoy 29-Feb-2000.
Oh well, I don't blame Microsoft for messing with Time. They're big
enough to claim the right. Emperors and Popes have done it too, and
they are all just famous dust now. As it says on the better old
maps, "Sic Transit Gloria Mundi," ("So passes away the glory of this
world" --the very words used in crowning a Pope!) but for us hoi
poloi who don't bestride the earth as giants, we know another famous
Latin phrase:
"Caveat emptor."
--
- Bill Thoen
------------------------------------------------------------
GISnet, 1401 Walnut St., Suite C, Boulder, CO 80302
tel: 303-786-9961, fax: 303-443-4856
mailto:[EMAIL PROTECTED], http://www.gisnet.com
------------------------------------------------------------
_______________________________________________________________________
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, send e-mail to [EMAIL PROTECTED] and
put "unsubscribe MapInfo-L" in the message body.
