copying dates in excel 2004

[simonsmith17]

copying dates (either by cutting and pasting cells or by copying
worksheets to a new workbook) results in dates being changed.
example
copying 3 cells containing:
11-May
12-May
13-May
to a new workbook, they read as:
12-May
13-May
14-May

or (even more bizarrely) copying:
10-May-07
11-May-07
12-May-07
becomes
11-May-11
12-May-11
13-May-11

this makes excel completely unuseable as an accurate record-keeping
tool.

Anyone got any ideas?

 

[little_creature]

Hmmm. Work fine for me. What cell format do you use (format>cell)?

 

[JE McGimpsey]

copying 3 cells containing:
11-May
12-May
13-May
to a new workbook, they read as:
12-May
13-May
14-May

XL has two date systems - the original (and Mac default) 1904 date
system, and the WinXL default 1900 date system.

Dates are simply integer offsets from the base date (either 1/1/1904 or
31 Dec 1899), so, for instance, 12 Jan 2007 to XL is 37632 in the 1904
date system, representing 37632 days after 1/1/1904. But in the 1900
system, it's 39094, the difference being the 1462 days between the two
base dates.

To prevent the conversion, make sure both workbooks are using the same
date system (In Preferences/Calculation, check or uncheck the 1904 date
system checkbox).

Or, to convert them in place, add or subtract 1462 to/from the date. You
can do this for a block of dates by entering 1462 in an empty cell,
copying the cell, selecting the block, and choosing Edit/Paste Special.
Select the Values and Add radio buttons, then click OK.

Note that there were really only 1461 days between the actual base
dates, but the 1900 system includes a phantom leap day (29 Feb 1900).