Copies 02/22/2005, Pastes 02/21/2001

[Carole O]

Excel 2003

Why does the date change? I copy 02/22/2005 and open a new spreadsheet to
paste and the result is 02/21/2001.

The formatting for both cells is the same. Any ideas?

TIA,
Carole O

 

[Harlan Grove]

Carole O wrote...

Excel 2003

Why does the date change? I copy 02/22/2005 and open a new spreadsheet to
paste and the result is 02/21/2001.

The formatting for both cells is the same. Any ideas?

Different date systems. The first file from which you're copying dates
is using the 1904 date system. Your system defaults to the 1900 date
system, so when you open a new workbook and paste into it, the date
value remains the same, but it represents a given number of days from a
different initial (epoch) date.

You could either change your default date system to 1904, or after
pasting into the new workbook enter 1/1/1904 in a blank cell, copy it,
select all date cells, Edit > Goto > Special > Constants, and Edit >
Paste Special > Values-Add to adjust all date constants to correct 1900
date system date serial values.

 

[Ron de Bruin]

Hi Carole

Both workbooks use a different date system
See Tools>Options..Calculations (1904 date system)

 

[Myrna Larson]

The first workbook is set to use the 1904 date system; the 2nd is set to use
the the 1900 system. To copy and paste between workbooks, they must use the
same date system. Fix this at Tools/Options/Calculation. If you change the
source workbook to 1900 system, you'll have to add 1462 to all of the existing
dates. If that's a hassle, change the destination book to the 1904 system.

BTW, the standard for Windows's Excel is the 1900 system (Use 1904 date system
is NOT checked). OTOH, Excel for the MacIntosh uses the 1904 system.