Excel question

bigboy99

macrumors 6502
Original poster
Nov 12, 2005
406
0
Sarasota, FL
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?
 

bigboy99

macrumors 6502
Original poster
Nov 12, 2005
406
0
Sarasota, FL
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.
 

mkrishnan

Moderator emeritus
Jan 9, 2004
29,641
12
Grand Rapids, MI, USA
I wonder if it has to do with this snippet from the Wiki article:

optionally Microsoft Excel can also use the Apple Macintosh epoch, which avoids the complication by starting later; it represents times by a number that is 1462 less.
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.
 

bigboy99

macrumors 6502
Original poster
Nov 12, 2005
406
0
Sarasota, FL
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.
 

Applespider

macrumors G4
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.
 

bigboy99

macrumors 6502
Original poster
Nov 12, 2005
406
0
Sarasota, FL
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.
 

Mackey

macrumors newbie
Apr 26, 2006
15
0
NJ
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.
 

bigboy99

macrumors 6502
Original poster
Nov 12, 2005
406
0
Sarasota, FL
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.