Excel question

Discussion in 'Mac Apps and Mac App Store' started by bigboy99, Oct 7, 2006.

  1. bigboy99 macrumors 6502

    Joined:
    Nov 12, 2005
    Location:
    Deep in the Heart of Tejas
    #1
    When copying and pasting text (dates and names) from one file to another the dates are going back one day (July 1 becomes June 30, 30th becomes 29, etc.), and the year becomes 2002 instead of 2006.

    What's going on?
     
  2. Applespider macrumors G4

    Applespider

    Joined:
    Jan 20, 2004
    Location:
    looking through rose-tinted spectacles...
    #2
    Could be something to do with the different reference dates between OS versions if the original data came from another system?

    OS X and Windows are about 4 years and a day apart thanks to the artificial leap year. Try adding 1462 to all the data and then cutting and pasting it...
     
  3. bigboy99 thread starter macrumors 6502

    Joined:
    Nov 12, 2005
    Location:
    Deep in the Heart of Tejas
    #3
    Interesting. I've been using Office/OSX since I went Mac last November, and this issue may have come up only once before. I can usually copy paste all data without any issues, both with Command V, and Paste Special, but now this issue happens with both commands.
     
  4. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #4
    I wonder if it has to do with this snippet from the Wiki article:

    I.E. Numbers being converted back and forth from Mac epoch to Excel epoch? I have some dim memory of having issues with this once that had to do with the datedif command, but now I'm not 100% sure what exactly had happened.
     
  5. bigboy99 thread starter macrumors 6502

    Joined:
    Nov 12, 2005
    Location:
    Deep in the Heart of Tejas
    #5
    In preferences there is a selection for "1904 date system." This changes all the dates in the column to 2002, and back to 2006 if selected. I don't understand the "epoch" issue, but it seems to be related to my problem.
     
  6. Applespider macrumors G4

    Applespider

    Joined:
    Jan 20, 2004
    Location:
    looking through rose-tinted spectacles...
    #6
    Basically, Excel doesn't store the date as 9 October 2006 but as a number which happens to format into that - it's somewhere in the 38000s now if you go with the non-1904 epoch

    The point that it starts counting from is the epoch - either 31 December 1899 or 1 Jan 1900 or Jan 1904 - so the same 38564 could be two different dates depending on when the system started counting for.
     
  7. bigboy99 thread starter macrumors 6502

    Joined:
    Nov 12, 2005
    Location:
    Deep in the Heart of Tejas
    #7
    The 1462 addition worked, but I dreaded having to enter that into every cell. Even the thought of figuring out a work around was dark. After resetting the 1904 preference option the copy/paste works correctly. Ghost in the machine again. Thanks to all for the insight.
     
  8. Mackey macrumors newbie

    Joined:
    Apr 26, 2006
    Location:
    NJ
    #8
    Quick way to add the date adjustment value

    You don't have to add the value to all those cells. Type the value in an empty cell and copy it. Then select all the dates in question and Edit / Paste Special and choose "values" and "add" from the selections.
     
  9. bigboy99 thread starter macrumors 6502

    Joined:
    Nov 12, 2005
    Location:
    Deep in the Heart of Tejas
    #9
    That's it! Actually, playing with the "1904" check box in Prefs fixed the issue, but it was terrifying at first. I think the 4.8 update caused OS to hiccup. All is well again.
     

Share This Page