1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel's Date System is WRONG!?!

Discussion in 'All Other Software' started by RAW Bits, Jul 19, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. RAW Bits

    RAW Bits Thread Starter

    Joined:
    Nov 23, 2004
    Messages:
    11
    (Note that I am using Excel 2002.)
    Try this: Type "2/28/1900" in a cell. Change it to a number. It will be 59. Do the same with "2/29/1900". Note that this is not a legal date, even though Excel does not complain. (1900 is not a leap year!) The number you get is 60. Now try "3/1/1900". Why is the corresponding number 61???
    I believe :rolleyes: that all date numbers after 2/28/1900 are too large by one!
    Has anyone else noticed this? Am I missing something here? I checked the "Software" forum and found nothing in the past 6 years. I would be amazed if I were the first to notice this!
     
  2. JohnWill

    JohnWill Retired Moderator

    Joined:
    Oct 19, 2002
    Messages:
    106,418
    I'm just curious as to the utility of changing a date to a number, and why this would be significant. :confused: I'm not sure there is any defined behavior for this conversion, what are you trying to accomplish?
     
  3. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
  4. RAW Bits

    RAW Bits Thread Starter

    Joined:
    Nov 23, 2004
    Messages:
    11
    This is not "changing a date to a number", but the basic definition of values that are (mis-)represented as dates in Excel. My original post, "change it to a number" referred to merely changing the format in the cell, not doing any computations.
    Mathematically:
    JohnWill says: "I'm not sure there is any defined behavior for this conversion."
    The Excel help system claims that "Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. The basic mathematical fact is that the above is FALSE! 1/1/2008 is actually 39,447 days after 1/1/1900. (Actually the values are correct from 1/1/1900 through 2/28/1900.) (Actually, the correct values represent days since 1/"0"/1900, or 12/31/1899.)
    Practically:
    Astrological computations often have calculation of Julian Day buried in them. You would assume from Excel's statement that the date in an Excel cell could be used directly. Also, any date range that spans 2/28/1900 will give incorrect answers. Put 2/28/1900 and 3/1/1900 (adjacent days) in cells and form their difference in a 3rd cell. Why is the answer 2? Even more suprising, try feeding these dates to the DATE360 function. So yes, there are consequences :eek: for the unwary.
     
  5. RAW Bits

    RAW Bits Thread Starter

    Joined:
    Nov 23, 2004
    Messages:
    11
    WendyM: This has nothing to do with Lotus 123 compatibility. It would be nice to know if Lotus got it right, though!
     
  6. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    It has everything to do with Lotus 1-2-3 compatibility, because Lotus did it wrong. Therefore Excel decided to do it wrong too, since it would be more compatible and the advantages outweighed the disadvantages. If you click the link in my post above, you can see Microsoft's explanation.
     
  7. RAW Bits

    RAW Bits Thread Starter

    Joined:
    Nov 23, 2004
    Messages:
    11
    I apologize! And thanks for the info, WendyM. Knowing the problem, I can get around it; I was just annoyed that I had been lied to!
    I've put it out there. Now I guess I can close this thread.
     
  8. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    I'm not sure why, but this made me laugh out loud. :) I can sympathize. I HATE it when numbers are wrong!
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/484424

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice