dates get corrupted when copied to another workbook

[Guest]

I copy a worksheet in a workbook to a new workbook, a date of 10/13/03 becomes 10/22/99, off by 4 years and a day. A cell by cell copy has the same resuly. Never seen that before. I am using excel xp
anyone seen this before??

 

[Dave Peterson]

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.

 

[Chip Pearson]

Jim,

Excel supports two date systems: one uses 1-Jan-1900 as the basis of dates,
and the other uses 1-Jan-1904 as the basis of dates. This setting is
specific to each workbook. In each workbook, go to the Tools menu, choose
Options, then the Calculation tab. There, ensure that the "1904 date system"
setting is the same for all workbooks. In general, this setting should not
be enabled; its primary purpose is for compatibility with Macintosh systems.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

I copy a worksheet in a workbook to a new workbook, a date of 10/13/03

becomes 10/22/99, off by 4 years and a day. A cell by cell copy has the same
resuly. Never seen that before. I am using excel xp

 

[Guest]

That worked, I simply change the format to 1904 for this worksheet. Turns out the original was developed on a mac

Thanks