Excel question

[bigboy99]

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?

 

[Applespider]

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

 

[bigboy99]

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]

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]

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]

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]

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]

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]

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.