≡ Menu

A Leap Back

1/23/2007 — A translation of this post, in Spanish has been provided by a reader. You can find it in the Los Trylobytes blog.

I’ve also taken this opportunity to update page and section references to refer to the final approved version of the Ecma Office Open XML specification, as well as providing a link to the final specification.


Early civilizations tried to rationalize the motions of the heavenly bodies. The sun rises and sets and they called that length of time a “day”. The moon changes phases and they called a complete cycle a “month”. And the sun moves through the signs of the zodiac and they called that a “year”. Unfortunately, these various lengths of time are not nice integral multiples of each other. A lunar month is not exactly 30 days. A solar year is not exactly 12 lunar months.

To work around these problems, civil calendars were introduced — some of the world’s first international standards — to provide a common understanding of date reckoning, without which commerce, justice and science would remain stunted.

In 45 B.C., Julius Caesar directed that an extra day be added to February every four years. (Interestingly, this extra day was not a February 29th as we have today in leap years, but by making February 24th last for two days.) This Julian System was in use for a long time, though even it has slight errors. By having a leap year every four years, we had 100 leap years every 400 years. However, to keep the seasons aligned properly with church feasts, etc., (who wants to celebrate Easter in Winter?) it was necessary to have only 97 leap years every 400 years.

So, in 1582 Pope Gregory XIII promulgated a new way of calculating leap years, saying that years divisible by 100 would be leap years only if they were also evenly divisible by 400. So, the year 1600 and 2000 were leap years, but 1700, 1800 and 1900 were not leap years. This Gregorian calendar was initial adopted by Catholic nations like Spain, Italy, France, etc. Protestant nations pretty much had adopted it by 1752, and Orthodox countries later, Russia after their 1918 revolution and Greece in 1923.

So, for most of the world, the Gregorian calendar has been the law for 250-425 years. That’s a well-established standard by anyone’s definition. Who would possibly ignore it or get it wrong at this point?

If you guessed “Microsoft”, you may advance to the head of the class.

Datetimes in Excel are represented as date serial numbers, where dates are counted from an origin, sometimes called an epoch, of January 1st, 1900. The problem is that from the earliest implementations Excel got it wrong. It thinks that 1900 was a leap year, when clearly it isn’t, under Gregorian rules since it is not divisible by 400. This error causes functions like the WEEKDAY() spreadsheet function to return incorrect values in some cases. See the Microsoft support article on this issue.

Now I have no problems with that bug remaining in Excel for backwards compatibility reasons. That’s an issue between Microsoft and their customers and not my concern. However, I am quite distressed to see this bug promoted into a requirement in the Ecma Office Open XML (OOXML) specification. From Section 3.17.41 of SpreadsheetML Reference Material, page 3305 of the OOXML specification (warning 49MB PDF download!) , “Date Representation”:

For legacy reasons, an implementation using the 1900 date base system shall treat 1900 as though it was a leap year. [Note: That is, serial value 59 corresponds to February 28, and serial value 61 corresponds to March 1, the next day, allowing the (nonexistent) date February 29 to have the serial value 60. end note] A consequence of this is that for dates between January 1 and February 28, WEEKDAY shall return a value for the day immediately prior to the correct day, so that the (nonexistent) date February 29 has a day-of-the-week that immediately follows that of February 28, and immediately precedes that of March 1.

So the new OOXML standard now contradicts 400 years of civil calendar practice, encodes nonexistent dates and returns the incorrect value for WEEKDAY()? And this is the mandated normative behavior? Is this some sort of joke?

The “legacy reasons” argument is entirely bogus. Microsoft could have easily have defined the XML format to require correct dates and managed the compatibility issues when loading/saving files in Excel. A file format is not required to be identical to an application’s internal representation.

Here is how I would have done it. Define the OOXML specification to encode dates using serial numbers that respect the Gregorian leap year calculations used by 100% of the nations on the planet. Then, if Microsoft desires to maintain this bug in their product, then have Excel add 1 to every date serial number of 60 or greater when loading, and subtract 1 from every such date when saving an OOXML file. This is not rocket science. In any case, don’t mandate the bug for every other processor of OOXML. And certainly don’t require that every person who wants the correct day of the week in 1900 to perform an extra calculation.

Sure this requires extra code to be added to Excel. Excel has a bug. Of course it will require code to fix a bug. Deal with it. I think the alternative of forcing the rest of the world to a adopt a new calendar system is the ultimate in chutzpah. The burden of a bug should fall on the product that has the bug, not with everyone else in the world.

Further, I’d note that section 3.2.28 (page 2693) defines a workbookPr (Workbook Properties) element with several attributes including the following flag:

date1904 (Date 1904)

Specifies a boolean value that indicates whether the date systems used in the workbook starts in 1904.

A value of on, 1, or true indicates the date system starts in 1904.
A value of off, 0, or false indicates the workbook uses the 1900 date system, where 1/1/1900 is the first day in the system.

The default value for this attribute is false.

What is so special about 1904 you might ask? This is another legacy problem with Excel, that implementations of Excel on the Mac, for reasons unknown to me, had an internal date origin of January 1st, 1904 rather than January 1st, 1900. This is unfortunate for Microsoft’s Mac Business Unit, and has likely been a source of frustration for them, needing to maintain these two date origins in their internal code.

But why is this my problem? Why should a standard XML format care about what Excel does on the Mac? Why should it care about any vendor’s quirks? If RobOffice (a fictional example) wants to internally use a date origin of March 15th, 1903 then that is my business. In my implementation I can do whatever I want. But when it comes to writing a file format standard, then the caprices of my implementation should not become a requirement for all other users of the file format. Further, if I cannot make up my mind and choose a single date origin then my indecisions should not cause other implementations to require extra code because of my indecision.

So there you have it, two ways in which Microsoft has created a needlessly complicated file format, and made your life more difficult if you are trying to work with this format, all to the exclusive advantage of their implementation. I wish I could assure you that this is an isolated example of this approach in OOXML But sadly, it is the rule, not the exception.

Creative Commons License
This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.

Comments on this entry are closed.

  • hAl 2006/10/13, 07:36

    “The goal is to enable the implementation of the Office Open XML formats by the widest set of tools and 5
    platforms, fostering interoperability across office productivity applications and line-of-business systems, as well 6
    as to support and strengthen document archival and preservation, all in a way that is fully compatible with the 7
    large existing investments in Microsoft Office documents”

    The legacy support goals seems the reason for those specific features in the OOXML format. Allthough those features might not allways be optimal the format makes it easy to build applications that are MS Office en OOXML compatible.

    On the other hand ODF is more basicly written down but I am not sure there is a single implementation that is really compatible with those general specs, not even OOo’s.

    I asume that ODF is going to use reference format implementations in order to create interoperability. However that will create simular quirks as reference implementations will create their own limitations.

    Both formats are on their first versions. We’ll see how the future will look.

  • Rob 2006/10/13, 10:39

    hAl,

    What does it mean to “be compatible with the large existing investments in Microsoft Office documents”? Whose investments? End Users? Or Microsoft’s?

    You could argue that there is a large investment in binary file formats, the old XSL, DOC, PPT files. But a large investment is not the same as large current value. I had an investment in groceries last month, but that investment is not worth much to me today. Most legacy documents will remain in their binary formats forever and will never be touched again.

    In what sense is OOXML compatible with the binary formats? It certainly is not identical. One is a binary record based format, and the other is XML files in a zip archive. Obviously a significant amount of work is required by Microsoft or anyone else who wants to update an application designed to work with the binary formats to use their new XML formats.

    When I think of “compatible”, I think that this means that the feature sets map, that in some sense there is a logical mapping that can be made from all legacy binary files into OOXML. And preferably the mapping can be done in an efficient way, using normal development tools.

    Is that a fair interpretation?

    I’m not arguing against compatibility. Creating a compatible format is clearly what TC45’s charter limited them to doing. However, being compatible is not the same as being identical. A format is not the same as an application implementation. They could have easily made a format that respected leap year conventions while at the same time remaining compatible with Excel. It would have been as simple as changing code in Excel 2007 (a product still under development) so it treats dates correctly when reading and writing OOXML format. The existing binary formats would not need to change and would continue to work as they do today.

    Instead of simply fixing this bug, they’ve instead written the bug into the standard, so now everyone else must do the same workaround in their code that Microsoft refused to do in their own. This isn’t about compatibility.

  • Anonymous 2006/10/14, 17:58

    hal, with my respect , this issue is “indefendible” … its an implementation bug and should not be promoted as an international standard

    i understand MS hurry to catch-up the standards train, but certain things are mmm.. inexcusables

  • orcmid 2006/10/15, 12:29

    I can confirm that the discussion of the 1900 and 1904 systems is in the final draft (I call it 1.5). Section 3.17.4.1 has the details, on pages 2522-2523.

    I also agree about the need to find a solution that leads to a rational, cleanly-defined specification.

    The problem of course, one already acknowledged in the specification, that it is necessary to support each deviation because a document might have it.

    It gets even more interesting if one wants to use different calendars altogether (in which case, I suppose, there should be a calendar attribute and the 1900-based one and the 1904-based one would be two flavors of Gregorian calendar. This, of course, makes the problem of registering and understanding different calendars an open problem for all users of the format.

    Bob Bemer was fond of saying that “standards are arbitrary solutions to recuring problems.” I don’t think he had in mind spreading the problem to those who don’t have it.

    In this case, it would be great to confine the bug in the way you describe. It’s clean, simple, and confines the problem to those who already have it.

    Your account also provides a great example of the pitfalls of confusing an implementation with the specified behavior (the “code rules” syndrome). A great benefit of interoperability standards is avoiding institutionalization of industrial accidents, not just under-specification of essential behavior.

  • Anonymous 2006/10/15, 15:10

    Well, we all know the 1904 issue arose because Apple in its wisdom (or otherwise) chose to start the OS epoch on the first leap year in the 20th century. At that time MS was happy to oblige potential clients and fudged a workaround. Of course there’s no way either of these fudged workarounds should get into an industry standard, unless your name is MS and you’re writing the standard…

  • Bigchris 2006/10/15, 15:18

    Microsoft told the EU “The code is the standard.” Apparently they really are foolish enough or arrogant enough to believe their code is chiseled in stone for all of mankind to worship.

  • Anonymous 2006/10/15, 19:58

    Joel Spolsky wrote a blog entry on a closely related topic earlier this year. You may find it an interesting read too: http://joelonsoftware.com/items/2006/06/16.html

  • Anonymous 2006/10/16, 06:25

    Orcmid, how can you possibly say that this behaviour is necessary because “a document might have it”? Precisely which documents do you have in mind? There precisely zero significant users of OOXML at present, because there is no non-beta product that uses the format.

  • Rob 2006/10/16, 08:48

    “If this were a major issue, it would’ve been dealt with.”

    That reminds me of an old joke. Two economists are walking down the street. One sees a $20 bill on the ground and starts to bend over to pick it up. But the other economist says, “Stop, don’t bother. If it were real someone would have already taken it.”

    In any case with a specification of this length, rushed through standardization at this speed, adhearing to a belief that every detail of OOXML was discussed and debated — this demonstrates childlike faith.

    Why is not the Ecma TC45 discussion forum open for the public to read? Why are not the meeting agends and minutes open for the public to read? Why are not the public comments submitted to TC45 with criticisms on the specification open to the public to read? Why is not the issues list the TC used to track problems in the specification open for the public to read?

    In the end, if you have a closed, private specification process which excludes public scrutiny, you cannot then argue that that same process gives you immunity from criticism.

  • David A. Wheeler 2006/10/16, 10:31

    OpenDocument does it right, while Microsoft’s XML format is in direct conflict with international standards. OpenDocument stores dates as, well, dates, in ISO 8601 format. The OpenFormula specification SPECIFICALLY does not require that the year 1900 be implemented incorrectly, avoiding this mistake.

    This is just yet another example of why you need to have many different implementors to create a standard (in practice, only one vendor is creating Microsoft’s XML format). Microsoft is simply describing their external API, an API for a single application… not an API that is appropriate for use as an international standard for arbitrary applications.

  • hAl 2006/10/16, 11:05

    “OpenDocument stores dates as, well, dates, in ISO 8601 format”

    Using that format is useless in a spreadsheet. It effectivly means all dates need to be converted to a sequential format internally when reading the file.
    Further more I asume that ODF does not really support ISO 8601 but only the w3c recommentation on dates (a subset of 8601) or else it would really be hellish to implement it fully.
    Allthough the OOXML isn’t pretty the use of ISO 8601 format is just useless for anything to do with computerprograms. It is closer to a presentation format than a computer format.
    Can’t beleave anyone would seriously use 8601 for storing spreadsheet dates. That makes that weird OOXML choice look like a breeze to computerprogrammers.

  • Anonymous 2006/10/16, 11:12

    Interesting example of how you can be stuck with bad decisions for a long long time…

    I’m pretty sure the origin of the 1-1-1900 bug is actually in the original IBM PC internal clock chip, which tried to work like IBM’s Big Iron at the time. Not to say that Microsoft couldn’t have done a cleaner coding job of interpreting dates properly.

    The 1-1-1904 difference came from the original clock chip used in the Mac. Apple decided to use a cheaper clock chip by using one with a start date of 1-1-1904, thereby avoiding the need for clock chip circuitry that dealt with leap years (by starting at 1904, every year that is divisible by 4 is a leap year until the year 2100, so the circuitry in the clock chip is less complex and therefore cheaper). My guess is Apple didn’t think anyone would still be using an original Mac by 2100). Microsoft, of course, took another shortcut here. Instead of making Excel for the Mac work the same as Excel for Windows, they gave you a manual switch inside the program. To this day, you can really confuse yourself by switching the date switch and watch all of your dates change in a file shared between Windows and the Mac.

  • orcmid 2006/10/16, 15:06

    anonymous #1: “Orcmid, how can you possibly say that this behaviour is necessary because “a document might have it”? Sorry, I was unclear. Here’s what I had in mind:

    The more different calendar formats that are defined as usable by conformant creators of an OOX document, the more different formats there are that must be accepted by every conformant accepter of the OOX format. The current specification is quite clear that a conformant implementation must properly accept documents that are created using either the 1900 or the 1904 convention (see the note in the bottom paragraph on p.2522). If they added a third, correct since-1900, version in the future or even a wier variety of calendar systems, all of those would have to be accepted properly by an OOX implementation.

    OOps. Slaps forehead. I agree with Rob that the ideal way to curb the 1900 anomaly would be to avoid introducing it in OOX and let those who produce/consume anomalous binaries deal with it.

    Unfortunately, the problem is not with the mapping but with the use of serial numbers as disguises for calendar dates. Any user-written formulas have access to the serial numbers and can manipulate them based on tacit understanding of what the relationship to calendar dates happens to be.

    It is the disguise of dates as serial numbers that prevents Rob’s solution. It is very hard to detect and adjust all such formulas to work with a different calendar mapping, and that leads to interchange problems that may be very difficult to cure. The only reliable approach is to identify the actual mapping.

    So, I think we’re stuck with how its done in OOX for the sake of interchange reliability and fidelity. That’s how to preserve however many legacy spreadsheet documents that would be silently broken on export to an OOX with a different serial-number date mapping.

    anonymous #2: “I’m pretty sure the origin of the 1-1-1900 bug is actually in the original IBM PC internal clock chip, …” Uh, are you sure that the clock chip kept a calendar? Maybe it just counted a large value of some small units and matching of clock to calendar was handled in the BIOS or other software? I don’t remember, but I would be surprised if the clock chip was very smart. It just had to keep on ticking, not draw much battery power at all, and not roll over more than once while the computer was turned off for the maximum battery-life duration.

  • orcmid 2006/10/16, 16:19

    hAl and David A. Wheeler. To be specific about the ODF case, the office:date-value is recorded in the lexical format employed by the XML Schema for dates. So a date-value (a specific data type for ODF, not an integer or serial number) is of the form

    [-][c...]ccyy-mm-dd[Z|{+|-}hh:mm]

    and the dates are partially ordered according to some interesting rules because of the ways calendar days in different time zones may overlap and in how the absence of a time-zone qualifier is rather ambiguous.

    I am writing this on office:date “2006-10-16-07:00″ and it is also still “2006-10-16Z” for almost another hour.

    The office:date format is not restricted from having dates beyond year 9999 and before year 0000.

    This appealing generality will, of course, encounter practical difficulties in interchange. ANd the greatest challenge will be translation between systems that use serial numbers that are accessible in formulas and ones that treat date as a strong datatype without any exposed mapping to serial date numbers.

    I suppose someone will need to add functions for Serial1904 and Serial1900Excel, or some such, to accomplish interchange with Excel.

  • Rob 2006/10/16, 16:48

    orchmid,

    Some thoughts regarding user-defined functions in Excel. If Excel simply adjusts the serial number when loading the file, then the in-memory value of the serial number — the value the user-defined function would see — is exactly what legacy functions would expect. Nothing breaks.

    An analogy. All numbers in XML are stored as text. The number 2 is stored not as binary 10, but as binary 00110010 for the text character “2”. But there is no confusion for Excel add-ins, macros or user functions. Moving from binary Excel files to XML Excel files does not require any user code to be rewritten, because Excel parses the XML, recognizes which attributes represent numbers, and converts them from text to number.

    All I’m suggesting is Excel do the same thing with date serial numbers. Recognize that they are date serial numbers according to the recognized civil calendar and convert them into whatever internal representation Excel wants. This value may be different in Windows versus the Mac. And it will take into account their legacy need to support their year-1900 leap year bug. But the serialized file format would remain unamiguous, correct and easy to use.

  • hAl 2006/10/16, 17:04

    @orcmid
    That seems consistent with a the w3c subset of iso 8601 as I remember it.
    Not a very pleasant choice but better than full implementation of 8601 which would have been a disaster. Still as a bit of a semi-presentational format not very usefull in any spreadsheet fileformat.

  • orcmid 2006/10/16, 18:42

    Rob, I was thinking about the problem of exporting from the binary format to OOX, not the import case. The problem is with exporting formulas that are dependent on an understood mapping and the dependency is non-obvious. This is a classic disguise problem (probably not begun with Excel but going back even farther).

    The problem also happens on import, of course, and we can’t always know which imported formulas depend on the external sequence number mapping and must be restated somehow.

    In this respect, I think there’s already a problem having both 1900 and 1904 supported. I wonder how often those worlds collide during Spreadsheet interchange.

    In a spreadsheet that I use every day, I know to write formulas like

    =B15+(DATEVALUE(“2006-05-07″)-DATEVALUE(“2006-05-06″))

    for cell B14 (with most-recent date at the top of the form) and

    =CHOOSE(WEEKDAY(B14),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)

    for cell A14, so this should travel back and forth in OOX no matter what the game is. Maybe most date manipulations are easy to adjust without tricks to avoid knowing the representation like those I use. I do assume one can do arithmetic on date representations though.

    [By the way, when I import my .xls into Open Office Calc and save it as .ods, the OpenDocument Spreadsheet format, those formulas are carried over exactly into the OO.o implementation of ODF Table Cells and formulas, and the same dates are presented, in the same format, in the visible presentation of cell values. Something tells me that spreadsheet developers are already dealing with Excel's off-by-1 serial-number problem.]

    This reminds me of another problem commented on in my What We See Is Not What We Get.

  • John 2006/10/17, 01:03

    Eeep. Yet another bungled date/calendar implementation, especially as it still prevents anyone from inputting dates prior to 1900 (yes, there are reasons for doing this, ask your local history professor, archaeologist, economic historian, or astronomer).

    And I sincerely hope the Oasis format doesn’t ‘just store as dates’ either. It ignores the internationalisation issue that not everyone uses the Gregorian Calendar, e.g. the Islamic and Jewish calendars.

    There’s a standard, well known, and widely implemented way of recording dates and calendars in a completely neutral way, that is the Julian Day system as widely seen in astronomy. This separates days and calendars, numbers each day relative to a universal epoch day, and has formulas to convert day numbers to and from various calendar systems.

    You need to store the date itself solely as a day counter making for easy maths, and then store the required display calendar system separately and only apply the conversion formulas where needed.

    John.

  • orcmid 2006/10/17, 13:01

    John, the Excel and OOX schemes do in fact use serial dates (i.e., a form of Julian day-time), except the (accurate) range is limited to the the equivalent of Gregorian-Calendar interval March 1, 1900 to December 31, 1999. The OOX serial numbers are how date values are conveyed, not how they are presented, which is controlled separately.

    The ODF specification uses the ISOform Gregorian date forms, described earlier, as the way date values are conveyed in interchange, and again they may be presented differently. In ODF 1.0 there is no discussion of serial (i.e., Julian-style offsets) dates in the ODF handling of date-time.

    I have not checked how OpenFormula addresses the need to perform calculations with values of date-time types. ODF 1.0 does provide a separate datatype for intervals of time, and I presume that scheme applies when the values of time intervals are conveyed as such. I don’t know how and whether those are amenable to manipulation as arithmetic values in OpenFormula and in existing ODF-supporting spreadsheet applications.

  • orcmid 2006/10/17, 13:37

    Rob: I notice in some comments (not yours) that it may not be appreciated that the Excel serial numbers for all Gregorian dates later than February 28, 1900, are off by one, assuming an origin of January 1, 1900 as serial value 1.

    Now, I am not sure which is more painful in the target interchange use of OOX: using a correct scheme or acknowledging and perpetuating the Excel defect. It would be interesting to see how the people working on converters between OOX and ODF manage to deal with this.

    Meanwhile, for this particular case, I see a potential compromise.

    In the OOX specification, consider defining the default serial date representation such that

    1. The origin is December 30, 1899 (serial value 0)

    2. Being defined only for representation of dates from March 1, 1900 (lower-limit serial value 61) through December 31, 9999 (upper-limit serial value 2,958,465).

    That should allow interchange of existing formulas and also leave Microsoft Office Excel to deal with export of out-of-range serial numbers and with the problematic conversion of date February 29, 1900.

    In this particular case, it seems that declaring a documentation bug that is corrected without invalidating actual usage in a tremendous number of existing spreadsheets is the way to go. I’ll try this one out on TC45.

    Meanwhile, I don’t know what to say about the 1904 case. I think there is no easy way to be sure that formulas aren’t broken when going into a non-1904 application, even though the defined 1904-style range is trivially mappable into the revised 1900 range I propose above. Maybe the people at Apple and those working on converters will know better whether such cases are likely to occur in the wild.

  • orcmid 2006/10/17, 21:11

    In my reply to John I said that the OOX serial date “(accurate) range is limited to the the equivalent of Gregorian-Calendar interval March 1, 1900 to December 31, 1999.” The upper limit is December 31, 9999, as accurately reported in my post following that.

  • davidacoder 2006/10/25, 05:14

    Brian Jones from MS wrote a blog entry on why they decided to represent dates the way they did here. I would be interested what you make of his explanation. Does it make sense to you?

  • Rob 2006/10/25, 11:31

    What Brian mentions is the hard-coded use of date serial numbers, in his example something like this:

    =IF(TODAY()=39013, “Due Today!”, “Not Due Today!”)

    I’d note that this is a problematic formula, even in legacy Excel files. In particular, it will give different results on Excel on the Mac versus Excel in Windows. So anyone who wrote formulas like that has already foregone any hope of portability.

    Also, I see no good explanation for why the WEEKDAY() function is explicitly defined in the Ecma specification to give incorrect answers. Microsoft has obviously already conditionalized the function to work correctly on both the Mac and Windows. Making it work correctly according to the Gregorian Calendar would have been trivial. It amounts to an “If” statement.

    Microsoft can yell “40 billion documents!” all they want, but Monday remains Monday and Tuesday remains Tuesday. I’m generally an open-minded guy, but I don’t think any amount of rationalization will change that fact that the Gregorian Calendar has rather more weight in the world of standards than Microsoft’s errors, no matter how stubbornly they cling to them.

    They can’t use MathML — not good enough. Can’t use SVG — VML is needed for legacy reasons. XForms? Not even considered, they have their own InfoPath stuff. PDF? Nah. We have XPS. JPG? Nope. We’ll push Windows Media Photo Format. Gregorian Calendar? Sorry, Pope Gregory XIII’s scheme wasn’t good enough. They follow Pope Bill III.

    It just shows that there is no standard so stable, so well established, so universally adopted that Microsoft will not push it aside and attempt to replace it with their own.

  • Roberto 2006/10/29, 10:12

    I would like to see a real world example of how keeping this bug would help, because I just see it causing problems in the long run.

  • Anonymous 2006/11/27, 16:59

    I’m surprised nobody mentioned that other old joke:

    Q: How many Microsoft employees does it take to change a light bulb?

    A: None. Darkness is the new industry standard.

  • Anonymous 2006/11/28, 19:03

    You should read:
    http://www.joelonsoftware.com/items/2006/06/16.html

    “It’s a bug in Excel!” I exclaimed.

    “Well, not really,” said Ed. “We had to do it that way because we need to be able to import Lotus 123 worksheets.”

    “So, it’s a bug in Lotus 123?”

    “Yeah, but probably an intentional one. Lotus had to fit in 640K. That’s not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That’s really fast and easy. The Lotus guys probably figured it didn’t matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back.”

    “Aargh!” I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.

    Cheers!
    Daniel

  • Rob 2006/11/28, 21:23

    Funny, when I worked at Lotus the story I heard was the bug was originally there for compatibility with VisiCalc…

  • Anonymous 2006/12/01, 02:02

    Please read this, http://apfr.co.in/290103/oneworldone.htm
    published in 1999 on their print edition

  • Gerardo Lisboa 2006/12/12, 15:36

    Really, they should have separated dates from calendars.

    They could have specified a MS-Gregorian Calendar with the 1900 glitch and a MS-Mac-Gregorian Calendar with the 1904 epoch.

    And use internally the UTC epoch (which I _think_ should be correct).

  • Arne Vogel 2006/12/13, 19:26

    “Still as a bit of a semi-presentational format not very usefull in any spreadsheet fileformat.”

    The ODF date format can be directly used in standard XSLT date functions, the weird OOXML format cannot. So even though the need for a “semi-presentational” format in ODF is arguable, this particular format is much easier to convert to a real presentational format using standard XSLT.

  • Anonymous 2007/01/19, 19:32

    hAl – I’ve reread your posts several times, and it’s clear you simply cannot grasp the fact that the representation of a date in the file format does not have to, indeed should not, mirror the representation inside the working memory of the program.

  • Trylobytero 2007/01/23, 12:02

    Hi there! I’ve just translated this article of yours into Spanish, and posted it on my blog. I’ve liked it and i’ve thought it would be great having a Spanish version, you know, for .
    If you are in disagreement with this, just tell me.

  • Anonymous 2007/09/04, 03:35

    That the Gregorian calendar is used by 100% of the nations of the world is not entirely correct. There are a number of middle-eastern countries that still use other-that-Gregorian calendars.

  • Alan 2007/11/02, 03:41

    A comment on this subject was submitted by several of the national bodies see:
    http://www.dis29500.org/ie-2

  • Sebastian 2010/02/11, 21:11

    February is a short month and it has less than 30 days. As February being a little month, March starts on the same day as February unless it’s a leap year when March starts on the following day. (Incidentally, if February had 30 days, it would be a double leap year.)

  • Sebastian (again) 2010/06/10, 20:28

    February was shortened to add another day to August. August was originally a 30-day month called Sextillis.

  • tibetus 2011/06/02, 06:58

    The Gregorian calculations were also precise as they used a long tested measure of time. The latest solar calendar claims a difference of a bare 26 seconds with it. However, precision is an indeterminate concept. The later-day astronomers never let Pope Gregory rest in peace. They picked loopholes in his system – which he himself would have welcomed.