Dates changing when copying worksheet to new workbook

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I have a worksheet that contains dates (month and yr)and
other data. The dates are in the first column. When I
try to copy and paste this page to a new workbook, the
dates change.
Ex:
Jan-00
Feb-00

After copy and pasting
Dec-95
Jan-96

I've noticed the same thing occurs when copy and pasting a
chart from Excel to PPT. Does anyone know how to prevent
this from happening or why it occurs??

Thanks in advance.
 
Go to Tools-Options-Calculation tab. If the "1904 Date System" is checked,
uncheck it. If this doesn't work and it is NOT checked, then check it. This
should make both worksheets/workbooks run on the same date system.
 
Richard O. Neville wrote...
Go to Tools-Options-Calculation tab. If the "1904 Date System"
is checked, uncheck it. If this doesn't work and it is NOT checked,
then check it. This should make both worksheets/workbooks run
on the same date system.
Click to expand...
...

The source workbook uses the 1904 date system, so unchecking tha
option in it would change *all* dates in it to dates 4 years plus a da
earlier. That is, if cell A1 held the date 24-Aug-2004 under the 190
date system, and the date system were changed to the 1900 date system
then cell A1 would hold the date 23-Aug-2000. So changing the dat
system in the source workbook would *REQUIRE* changing all dat
constants in that workbook as well.

Also, this is only treating the symptoms. The cause is more importan
to fix. The OP apparently works in an organization in which there ar
multiple date systems in use. THAT'S BAD! Every business using Exce
should impose one & only one date system for ALL Excel files. If, a
the OP relates, PowerPoint can't cope with the 1904 date system, th
date system chosen should always be the 1900 date system, and Mac user
get the hell of having to fix all their workbooks (unless Mac PowerPoin
always uses the 1904 date system).

JE, where are you
 
From a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.
 
Back
Top