none
What is story behind December 30, 1899 as base date?

    Question

  • All,

    Just an curiosity question for me - What is story behind December 30, 1899 as base date?  I saw John Vinston made metion that it was Date 0 for Access.  I was curious why this date was choosen for date 0.

    Dennis

    Thursday, July 8, 2010 4:29 AM

Answers

  • Actually for the people here there is a little bit of a story about thus date choice.

     

    Logically a good starting date with the January 1, 1900, so why a off that date?

     

    It turns out that many years ago Lotus 123 was one of the more popular spreadsheets in the marketplace. It also turns out that Lotus 123 had a date calculation bug in which it assumed that the year 1900 was a leap year.

     

    So for issues of compatibility and making the calculations remain the same across the two products, Excel by design and on purpose adopted the same date calculation bug!

     

    So when they were setting up the date calculations system for VBA, there was a decision made to offset it by one day, as that would result in the same number of days between two different dates as what you get in Excel and Lotus.

     

    So in theory I can't really say the above is 100% true, but they simply just offset it by one day to be different and to reduce the confusion issue.

     

    There is some explains and details here:

     

    http://www.cpearson.com/excel/datetime.htm

     

    So, there is little bit of interesting history here, and it comes down to that January 1, 1900 really is a more logical choice, but above issues made this turn out different.

     

    edit:

    Gee, maybe I should read that link!

     

    The value of 0 is NOT 1899.

     

    Try:

     

    ? format(cdate(0),"General date")

    12:00:00 AM

     

    ? format(cdate(1),"General date")

    12/31/1899

     

    You note that the value of 1 is 1899, 0 in the above returns no date at all. So, my "guess" about the 1<sup>st</sup> day being offset by one seems reasonable, but I would not quote me as a expert on the above  ;-)

     

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by DenSolis Tuesday, July 13, 2010 2:43 PM
    Saturday, July 10, 2010 1:58 PM

All replies

  • Hi,
    it is not for Access only, but for Foundation Class Library, and windows,
    etc
    see:
    http://en.wikipedia.org/wiki/Epoch_(reference_date)#Computing
     
    --
    Best regards,
    ___________
    Alex Dybenko (MVP)
     "DenSolis" wrote in message
    news:f1eef5fe-ef5e-4ab6-9d92-0998d3fa6e14@communitybridge.codeplex.com...
    All,
    Just an curiosity question for me - What is story behind December 30, 1899
    as base date?  I saw John Vinston made metion that it was Date 0 for Access.
    I was curious why this date was choosen for date 0.
    Dennis
     
     
    Thursday, July 8, 2010 1:37 PM
  • Actually for the people here there is a little bit of a story about thus date choice.

     

    Logically a good starting date with the January 1, 1900, so why a off that date?

     

    It turns out that many years ago Lotus 123 was one of the more popular spreadsheets in the marketplace. It also turns out that Lotus 123 had a date calculation bug in which it assumed that the year 1900 was a leap year.

     

    So for issues of compatibility and making the calculations remain the same across the two products, Excel by design and on purpose adopted the same date calculation bug!

     

    So when they were setting up the date calculations system for VBA, there was a decision made to offset it by one day, as that would result in the same number of days between two different dates as what you get in Excel and Lotus.

     

    So in theory I can't really say the above is 100% true, but they simply just offset it by one day to be different and to reduce the confusion issue.

     

    There is some explains and details here:

     

    http://www.cpearson.com/excel/datetime.htm

     

    So, there is little bit of interesting history here, and it comes down to that January 1, 1900 really is a more logical choice, but above issues made this turn out different.

     

    edit:

    Gee, maybe I should read that link!

     

    The value of 0 is NOT 1899.

     

    Try:

     

    ? format(cdate(0),"General date")

    12:00:00 AM

     

    ? format(cdate(1),"General date")

    12/31/1899

     

    You note that the value of 1 is 1899, 0 in the above returns no date at all. So, my "guess" about the 1<sup>st</sup> day being offset by one seems reasonable, but I would not quote me as a expert on the above  ;-)

     

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by DenSolis Tuesday, July 13, 2010 2:43 PM
    Saturday, July 10, 2010 1:58 PM
  • Because obviously Lotus fogot about the rule that a year is a leap year if it is evenly divisible by 400.
    David H
    Saturday, July 10, 2010 3:38 PM