Why are Excel weekdays wrong for 1900?












27














This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel?



When A1 has the value 2009-08-01, then:





  • =WEEKDAY(A1) will obtain 7


  • =TEXT(7, "dddd") will obtain Saturday


  • =TEXT(7,"dddd, yyyy-mm-dd") will obtain Saturday, 1900-01-07


  • =TEXT(1,"dddd, yyyy-mm-dd") will obtain Sunday, 1900-01-01


  • =TEXT("1900-01-01","dddd, yyyy-mm-dd") will also obtain Sunday, 1900-01-01


The last two are wrong: the 1st of January 1900 is actually a Monday.

Various sources seem to confirm that:




  • January 1900 - Wikipedia, the free encyclopedia.

  • Year 1900 Calendar – The Netherlands.

  • Year 1900 Calendar – United States.


What am I missing? Why is Excel doing this wrong?










share|improve this question




















  • 1




    Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
    – AdamV
    Oct 2 '12 at 11:10
















27














This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel?



When A1 has the value 2009-08-01, then:





  • =WEEKDAY(A1) will obtain 7


  • =TEXT(7, "dddd") will obtain Saturday


  • =TEXT(7,"dddd, yyyy-mm-dd") will obtain Saturday, 1900-01-07


  • =TEXT(1,"dddd, yyyy-mm-dd") will obtain Sunday, 1900-01-01


  • =TEXT("1900-01-01","dddd, yyyy-mm-dd") will also obtain Sunday, 1900-01-01


The last two are wrong: the 1st of January 1900 is actually a Monday.

Various sources seem to confirm that:




  • January 1900 - Wikipedia, the free encyclopedia.

  • Year 1900 Calendar – The Netherlands.

  • Year 1900 Calendar – United States.


What am I missing? Why is Excel doing this wrong?










share|improve this question




















  • 1




    Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
    – AdamV
    Oct 2 '12 at 11:10














27












27








27


3





This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel?



When A1 has the value 2009-08-01, then:





  • =WEEKDAY(A1) will obtain 7


  • =TEXT(7, "dddd") will obtain Saturday


  • =TEXT(7,"dddd, yyyy-mm-dd") will obtain Saturday, 1900-01-07


  • =TEXT(1,"dddd, yyyy-mm-dd") will obtain Sunday, 1900-01-01


  • =TEXT("1900-01-01","dddd, yyyy-mm-dd") will also obtain Sunday, 1900-01-01


The last two are wrong: the 1st of January 1900 is actually a Monday.

Various sources seem to confirm that:




  • January 1900 - Wikipedia, the free encyclopedia.

  • Year 1900 Calendar – The Netherlands.

  • Year 1900 Calendar – United States.


What am I missing? Why is Excel doing this wrong?










share|improve this question















This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel?



When A1 has the value 2009-08-01, then:





  • =WEEKDAY(A1) will obtain 7


  • =TEXT(7, "dddd") will obtain Saturday


  • =TEXT(7,"dddd, yyyy-mm-dd") will obtain Saturday, 1900-01-07


  • =TEXT(1,"dddd, yyyy-mm-dd") will obtain Sunday, 1900-01-01


  • =TEXT("1900-01-01","dddd, yyyy-mm-dd") will also obtain Sunday, 1900-01-01


The last two are wrong: the 1st of January 1900 is actually a Monday.

Various sources seem to confirm that:




  • January 1900 - Wikipedia, the free encyclopedia.

  • Year 1900 Calendar – The Netherlands.

  • Year 1900 Calendar – United States.


What am I missing? Why is Excel doing this wrong?







microsoft-excel date






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 20 '17 at 10:17









Community

1




1










asked Sep 30 '12 at 14:43









Jeroen Wiert PluimersJeroen Wiert Pluimers

1,53483151




1,53483151








  • 1




    Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
    – AdamV
    Oct 2 '12 at 11:10














  • 1




    Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
    – AdamV
    Oct 2 '12 at 11:10








1




1




Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
– AdamV
Oct 2 '12 at 11:10




Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary.
– AdamV
Oct 2 '12 at 11:10










3 Answers
3






active

oldest

votes


















40














As described in Microsoft KB 214058:




Days of the week before March 1, 1900 are incorrect in Excel



MORE INFORMATION



When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.



However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:




Excel 2000 incorrectly assumes that the year 1900 is a leap year



MORE INFORMATION



When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.



When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.



Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.



If this behavior were to be corrected, many problems would arise, including the following:




  • Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.

  • Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.

  • Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.


If the behavior remains uncorrected, only one problem occurs:




  • The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.







share|improve this answer

















  • 9




    Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
    – matt
    Sep 30 '12 at 21:47








  • 4




    See also. Many programmers incorrectly assume that working with dates/times is easy :)
    – BlueRaja - Danny Pflughoeft
    Sep 30 '12 at 22:18








  • 2




    Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
    – Isaac Rabinovitch
    Oct 2 '12 at 17:46



















12














Here is the reason explained by Joel himself: My First BillG Review




Basic uses December 31, 1899 as the epoch instead of January 1, 1900,
but for some reason, today's date was the same in Excel as it was in
Basic.



Huh?



I went to find an Excel developer who was old enough to remember why.
Ed Fries seemed to know the answer.



"Oh," he told me. "Check out February 28th, 1900."



"It's 59," I said.



"Now try March 1st."



"It's 61!"



"What happened to 60?" Ed asked.



"February 29th. 1900 was a leap year! It's divisible by 4!"



"Good guess, but no cigar," Ed said, and left me wondering for a
while.



Oops. I did some research. Years that are divisible by 100 are not
leap years, unless they're also divisible by 400.



1900 wasn't a leap year.



"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.







share|improve this answer

















  • 1




    @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
    – Giorgi
    Oct 1 '12 at 18:18





















1














One solution to this is to add 400 years to the year, to work out the weekday as in the following formula
=WEEKDAY(DATE(A4+400,B4,C4),1)
so if
A4 =1834
B4 = 12
C4 = 14
this would return 1 (Sunday)
which is the same as for 14 December 2234
This stops working for dates before 1753 the year after the change to the Gregorian calendar






share|improve this answer





















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f481496%2fwhy-are-excel-weekdays-wrong-for-1900%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    40














    As described in Microsoft KB 214058:




    Days of the week before March 1, 1900 are incorrect in Excel



    MORE INFORMATION



    When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.



    However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




    The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:




    Excel 2000 incorrectly assumes that the year 1900 is a leap year



    MORE INFORMATION



    When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.



    When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.



    Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.



    If this behavior were to be corrected, many problems would arise, including the following:




    • Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.

    • Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.

    • Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.


    If the behavior remains uncorrected, only one problem occurs:




    • The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.







    share|improve this answer

















    • 9




      Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
      – matt
      Sep 30 '12 at 21:47








    • 4




      See also. Many programmers incorrectly assume that working with dates/times is easy :)
      – BlueRaja - Danny Pflughoeft
      Sep 30 '12 at 22:18








    • 2




      Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
      – Isaac Rabinovitch
      Oct 2 '12 at 17:46
















    40














    As described in Microsoft KB 214058:




    Days of the week before March 1, 1900 are incorrect in Excel



    MORE INFORMATION



    When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.



    However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




    The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:




    Excel 2000 incorrectly assumes that the year 1900 is a leap year



    MORE INFORMATION



    When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.



    When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.



    Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.



    If this behavior were to be corrected, many problems would arise, including the following:




    • Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.

    • Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.

    • Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.


    If the behavior remains uncorrected, only one problem occurs:




    • The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.







    share|improve this answer

















    • 9




      Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
      – matt
      Sep 30 '12 at 21:47








    • 4




      See also. Many programmers incorrectly assume that working with dates/times is easy :)
      – BlueRaja - Danny Pflughoeft
      Sep 30 '12 at 22:18








    • 2




      Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
      – Isaac Rabinovitch
      Oct 2 '12 at 17:46














    40












    40








    40






    As described in Microsoft KB 214058:




    Days of the week before March 1, 1900 are incorrect in Excel



    MORE INFORMATION



    When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.



    However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




    The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:




    Excel 2000 incorrectly assumes that the year 1900 is a leap year



    MORE INFORMATION



    When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.



    When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.



    Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.



    If this behavior were to be corrected, many problems would arise, including the following:




    • Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.

    • Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.

    • Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.


    If the behavior remains uncorrected, only one problem occurs:




    • The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.







    share|improve this answer












    As described in Microsoft KB 214058:




    Days of the week before March 1, 1900 are incorrect in Excel



    MORE INFORMATION



    When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.



    However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




    The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:




    Excel 2000 incorrectly assumes that the year 1900 is a leap year



    MORE INFORMATION



    When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.



    When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.



    Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.



    If this behavior were to be corrected, many problems would arise, including the following:




    • Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.

    • Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.

    • Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.


    If the behavior remains uncorrected, only one problem occurs:




    • The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.








    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Sep 30 '12 at 14:50









    grawitygrawity

    233k36493548




    233k36493548








    • 9




      Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
      – matt
      Sep 30 '12 at 21:47








    • 4




      See also. Many programmers incorrectly assume that working with dates/times is easy :)
      – BlueRaja - Danny Pflughoeft
      Sep 30 '12 at 22:18








    • 2




      Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
      – Isaac Rabinovitch
      Oct 2 '12 at 17:46














    • 9




      Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
      – matt
      Sep 30 '12 at 21:47








    • 4




      See also. Many programmers incorrectly assume that working with dates/times is easy :)
      – BlueRaja - Danny Pflughoeft
      Sep 30 '12 at 22:18








    • 2




      Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
      – Isaac Rabinovitch
      Oct 2 '12 at 17:46








    9




    9




    Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
    – matt
    Sep 30 '12 at 21:47






    Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html
    – matt
    Sep 30 '12 at 21:47






    4




    4




    See also. Many programmers incorrectly assume that working with dates/times is easy :)
    – BlueRaja - Danny Pflughoeft
    Sep 30 '12 at 22:18






    See also. Many programmers incorrectly assume that working with dates/times is easy :)
    – BlueRaja - Danny Pflughoeft
    Sep 30 '12 at 22:18






    2




    2




    Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
    – Isaac Rabinovitch
    Oct 2 '12 at 17:46




    Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet.
    – Isaac Rabinovitch
    Oct 2 '12 at 17:46













    12














    Here is the reason explained by Joel himself: My First BillG Review




    Basic uses December 31, 1899 as the epoch instead of January 1, 1900,
    but for some reason, today's date was the same in Excel as it was in
    Basic.



    Huh?



    I went to find an Excel developer who was old enough to remember why.
    Ed Fries seemed to know the answer.



    "Oh," he told me. "Check out February 28th, 1900."



    "It's 59," I said.



    "Now try March 1st."



    "It's 61!"



    "What happened to 60?" Ed asked.



    "February 29th. 1900 was a leap year! It's divisible by 4!"



    "Good guess, but no cigar," Ed said, and left me wondering for a
    while.



    Oops. I did some research. Years that are divisible by 100 are not
    leap years, unless they're also divisible by 400.



    1900 wasn't a leap year.



    "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.







    share|improve this answer

















    • 1




      @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
      – Giorgi
      Oct 1 '12 at 18:18


















    12














    Here is the reason explained by Joel himself: My First BillG Review




    Basic uses December 31, 1899 as the epoch instead of January 1, 1900,
    but for some reason, today's date was the same in Excel as it was in
    Basic.



    Huh?



    I went to find an Excel developer who was old enough to remember why.
    Ed Fries seemed to know the answer.



    "Oh," he told me. "Check out February 28th, 1900."



    "It's 59," I said.



    "Now try March 1st."



    "It's 61!"



    "What happened to 60?" Ed asked.



    "February 29th. 1900 was a leap year! It's divisible by 4!"



    "Good guess, but no cigar," Ed said, and left me wondering for a
    while.



    Oops. I did some research. Years that are divisible by 100 are not
    leap years, unless they're also divisible by 400.



    1900 wasn't a leap year.



    "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.







    share|improve this answer

















    • 1




      @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
      – Giorgi
      Oct 1 '12 at 18:18
















    12












    12








    12






    Here is the reason explained by Joel himself: My First BillG Review




    Basic uses December 31, 1899 as the epoch instead of January 1, 1900,
    but for some reason, today's date was the same in Excel as it was in
    Basic.



    Huh?



    I went to find an Excel developer who was old enough to remember why.
    Ed Fries seemed to know the answer.



    "Oh," he told me. "Check out February 28th, 1900."



    "It's 59," I said.



    "Now try March 1st."



    "It's 61!"



    "What happened to 60?" Ed asked.



    "February 29th. 1900 was a leap year! It's divisible by 4!"



    "Good guess, but no cigar," Ed said, and left me wondering for a
    while.



    Oops. I did some research. Years that are divisible by 100 are not
    leap years, unless they're also divisible by 400.



    1900 wasn't a leap year.



    "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.







    share|improve this answer












    Here is the reason explained by Joel himself: My First BillG Review




    Basic uses December 31, 1899 as the epoch instead of January 1, 1900,
    but for some reason, today's date was the same in Excel as it was in
    Basic.



    Huh?



    I went to find an Excel developer who was old enough to remember why.
    Ed Fries seemed to know the answer.



    "Oh," he told me. "Check out February 28th, 1900."



    "It's 59," I said.



    "Now try March 1st."



    "It's 61!"



    "What happened to 60?" Ed asked.



    "February 29th. 1900 was a leap year! It's divisible by 4!"



    "Good guess, but no cigar," Ed said, and left me wondering for a
    while.



    Oops. I did some research. Years that are divisible by 100 are not
    leap years, unless they're also divisible by 400.



    1900 wasn't a leap year.



    "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.








    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Oct 1 '12 at 18:13









    GiorgiGiorgi

    4592617




    4592617








    • 1




      @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
      – Giorgi
      Oct 1 '12 at 18:18
















    • 1




      @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
      – Giorgi
      Oct 1 '12 at 18:18










    1




    1




    @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
    – Giorgi
    Oct 1 '12 at 18:18






    @JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer.
    – Giorgi
    Oct 1 '12 at 18:18













    1














    One solution to this is to add 400 years to the year, to work out the weekday as in the following formula
    =WEEKDAY(DATE(A4+400,B4,C4),1)
    so if
    A4 =1834
    B4 = 12
    C4 = 14
    this would return 1 (Sunday)
    which is the same as for 14 December 2234
    This stops working for dates before 1753 the year after the change to the Gregorian calendar






    share|improve this answer


























      1














      One solution to this is to add 400 years to the year, to work out the weekday as in the following formula
      =WEEKDAY(DATE(A4+400,B4,C4),1)
      so if
      A4 =1834
      B4 = 12
      C4 = 14
      this would return 1 (Sunday)
      which is the same as for 14 December 2234
      This stops working for dates before 1753 the year after the change to the Gregorian calendar






      share|improve this answer
























        1












        1








        1






        One solution to this is to add 400 years to the year, to work out the weekday as in the following formula
        =WEEKDAY(DATE(A4+400,B4,C4),1)
        so if
        A4 =1834
        B4 = 12
        C4 = 14
        this would return 1 (Sunday)
        which is the same as for 14 December 2234
        This stops working for dates before 1753 the year after the change to the Gregorian calendar






        share|improve this answer












        One solution to this is to add 400 years to the year, to work out the weekday as in the following formula
        =WEEKDAY(DATE(A4+400,B4,C4),1)
        so if
        A4 =1834
        B4 = 12
        C4 = 14
        this would return 1 (Sunday)
        which is the same as for 14 December 2234
        This stops working for dates before 1753 the year after the change to the Gregorian calendar







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 14 '18 at 19:46









        AdrianAdrian

        111




        111






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f481496%2fwhy-are-excel-weekdays-wrong-for-1900%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Plaza Victoria

            Puebla de Zaragoza

            Musa