Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Special ADD dates (Excel 97 & 2003)

    This strikes me as odd.
    In a vacant cell I enter the date 25-Dec-2006 and format it to show the full 4-digit year.
    I copy that cell and paste it into an empty cell. Not surprisingly I see the same date.

    I re-copy the original cell and in the second cell choose Edit, paste Special, Add.
    The new value is 12/20/2113, that is, the date has not added from 1900, but from 2000. No wait! There's more!!

    The two cells can now be copied to third and fourth cells as paste Special, Value.
    Formatting the 3rd and 4th cells as numbers reveal 39076 and 78152 respectively; I had expected the 4th cell to show the numeric value 41628.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Paste Special ADD dates (Excel 97 & 2003)

    I'm surprised that you're surprised! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    You have copied 25-Dec-2006 (value 39076) to a cell. You then copy-paste special -add again to the same cell (so you've added 39076 and 39076) and you get 78152. Why would you expect to get 41628??
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special ADD dates (Excel 97 & 2003)

    > the date has not added from 1900, but from 2000

    It's exactly the other way round: the date has not been added from 2000, but from 1900 (from December 31, 1899 to be exact, this is the origin of the date system). Dates in Excel are stored as the number of days since December 31, 1899, i.e. 1 = January 1, 1900 etc. (with a little twist - 1900 is treated as a leap year for compatibility with Lotus 1-2-3). So the addition behaves exactly as it should.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Special ADD dates (Excel 97 & 2003)

    > I'm surprised that you're surprised!
    Both of you: I'm not surprised that I wasn't surprised. It was early in the morning, and cheap coffee to boot.

    My original Paste-Add was done with a format of mm-dd-yy, so it showed up as "12/20/13", and I read the "13" as "2013" in my mind.
    That set my mind to thinking 20xx, and even after formatting to show yyyy my stupid mind still saw 20xx. I have trouble remembering names, but am, apparently, very good at remembering things I've not seen. Go Figure.

    I quite agree that (roughly) 39K + 39K is (roughly) 80K
    Now. (or "Now()", as we say in the trade)


    >Why would you expect to get 41628?
    (Please see "stupid mind" above)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •