Why does the OLE variant date format use 30 December 1899 as its zero point?


In 2006, via the suggestion box, Chris J asks why the OLE variant date format has such a strange zero point. Its zero point is 30 December 1899, as opposed to 1 January 1900 (SQL Server's zero point) or 1 January 1970 (the unix zero point).

It turns out I don't have to answer this because Eric Lippert explained it three years before the question was posted, and then some time later posted a puzzle regarding date calculations.

Public Service Announcement: This weekend marks the start of Daylight Saving Time in most of the United States.

Comments (14)
  1. Alexandre says:

    Joel Spolsky also mentioned this situation a long time ago. Granted, it was in passing; the article was more focused on the trust Bill Gates puts in his employees.

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

  2. Something’s missing here.

    1/1/1900 is natural.  Fine.

    If Lotus 1-2-3 had a bug where 1900 was incorrectly considered as a leap year, that accounts for a single day of error.  So Visual Basic starts at 12/31/1899.  Fine.

    But from 12/30/1899 to 1/1/1900 is two days.  Where did the other day go?

  3. David Walker says:

    Days since midnight on 12/30/1899 (which is 00:00:00 on 12/31/1899) is only one day off from the start of 1/1/1900.  It all depends on your definition of "since" and how that applies to dates!

  4. Gabe says:

    Maurits: Lotus decided that day 1 would be 1/1/1900, which made day 0 12/31/1899. Then the incorrect leap year causes VB’s day 0 to be 12/30/1899.

  5. Anonymous Coward says:

    Regarding Joel’s article, I really love him for late binding, For Each and With.

    The two others, Date and Variant have their problems. I won’t hold those against him, since they were both requirements for being an Excel macro language.

    That said, Variants should have been kept local to the Excel library. That way they wouldn’t have infected Visual Basic proper and optional parameters and parameter arrays might have been implemented more sanely.

    The discontinuities in the Date format are horrible. If it was really necessary for Excel to emulate Lotus 1-2-3 that accurately (it still had to import / export if I recall correctly, so I’m not convinced) the weird format should have been kept local to Excel. Don’t get me wrong, Visual Basic is better for having a way to store dates, but a more linear format would have been better.

  6. W says:

    The discontinuities in the Date format are horrible. If it was really necessary for Excel to emulate Lotus 1-2-3 that accurately (it still had to import / export if I recall correctly, so I’m not convinced) the weird format should have been kept local to Excel.

    It probably was. Afaik the main problem with excel&co is lack of typing. You never know if a number represents a date, a normal number or something else, since that is only decided when it gets formatted for display.

  7. Roger Gammans says:

    In the UK the 1/1/1970 is a really bad example date to use for an epoch start. Well sort of.

    At least on library uses the epoch / epoch+6 months to test for daylight saving time rules.

    Now the really alert (or merely elderly) can see where I’m going with this but in 1970 the UK was in the middle of a two year trial of DST (summer time) and double summer time. So using the simple test based on this date and extrapolating is going to end in pain.

  8. kip says:

    @David Walker: "midnight on 12/30/1899 (which is 00:00:00 on 12/31/1899) is only one day off from the start of 1/1/1900"

    No, midnight on 12/30/1899 is 1899-12-30 00:00:00, which is exactly 48 hours (not 24!) before the start of 1/1/1900.

  9. Worf says:

    And that’s the problem with midnight. "Midnight on <date>" is inherently ambiguous.

    Midnight on March 12, 2010 – it could either refer to the time between March 11 and March 12, or March 12 and March 13. There is no standard.

    E.g., after 23:59:59.9999… and 00:00:00.00000… is midnight which technically belongs to both days. (Midnight can be defined as 24:00:00 the previous day, or 00:00:00 the following).

    That’s why contests ending at midnight tend to add an extra second or subtract a second to make it clear. "Contest closes 23:59:59 EST on <date> or "Contest starts 00:00:01 PDT on <date>" – it leaves no legal ambiguity

    The fact we’re arguing whether midnight Dec 30, 1899 is the period between Dec 29-30, or 30-31 is proof even we can’t get it right.

  10. >That said, Variants should have been kept local to the Excel library. That way they wouldn’t have infected Visual Basic proper and optional parameters and parameter arrays might have been implemented more sanely.

    Visual Basic had Variant variables in Version 2.0; VBA didn’t merge with it until Version 4, so VB had the Variant type before  that.

  11. Gabe says:

    How did MSFT set the 640k limit? It was IBM who decided to use Intel’s 8088 in the IBM PC and created the memory map which reserved the high 384k for device memory, leaving the low 640k of the 8088’s address space for use applications.

    Besides, it’s not like Lotus had to worry about fitting their app into 640k anyway. The original IBM PC, which Lotus 1-2-3 ran on, could only be expanded to hold 256k of RAM and 360k on the floppy drives. Of course Lotus wouldn’t sell much if they required top-of-the-line computers to run their product, so they had to run in 128k of RAM. And obviously that space had to include room for the user’s data, so Lotus 1-2-3 had to fit in well under 128k!

  12. 640k says:

    The reason why newly developed COM APIs in 2010 has this *BUG*? Because another company’s dos app had to fit in 640k ram? lol.

    Don’t blame this bugs on other defunct companies, MSFT was the company that set the 640k limit. Although it’s poetic justice that MS still has to eat it’s own dogfood after all this years.

    Not to say that MS has had the opportunity to fix this several times. But didn’t do it.

    1. Excel doesn’t have to emulate lotus123 bugs (could have used a better import function).

    2. Excel VBA doesn’t have to use Excel bugs.

    3. COM doesn’t have to use buggy Excel VBA dates.

    4. New interfaces in 2010 doesn’t have to use COM and it’s buggy dates.

    [It’s hardly a simply tweak to the import function. You have to reverse-engineer the formulas and macros, too, in order to know where to insert “+1” or “-1”. Example: Given the formula “LOOKUP(A1,D50:D90,E50:E90)”, do you need to do a +1 to compensate for the date bias? Items 2 and 3 introduce discontinuities. You are welcome to use 4 in your own interfaces; just accept that you can’t interoperate with anybody who does use the existing VARIANT type. -Raymond]
  13. Danny says:

    <<.. is midnight which technically belongs to both days. (Midnight can be defined as 24:00:00 the previous day, or 00:00:00 the following).

    Bad define…is the same as saying binary has number 2 or octal has number 8. After 1 follows 0 / after 7 follows 0. So is the same in time, you dont have 24, you have 23:59:59 and then 00:00:00 follows, and that is midnight. So midnight is the beginning, never the end.

    Now if someone is saying "midnight 30 Dec 1899" that is 48 hours before the year is finishing.

  14. Anonymous Coward says:

    Visual Basic had Variant variables in Version 2.0

    VB2 was released after Joel’s interview, so I highly suspect they were added to VB for consistency with Excel Basic.

    It probably was. …

    So if a cell is referenced by a date calculation function but is formatted as a double, it wouldn’t get imported properly. I think nowadays we would have solved this differently, but I see why they did it. That doesn’t excuse Basic though.

Comments are closed.

Skip to main content