date-1 day when copying

[Andrei]

When I copy date fields from one sheet into another, date fields in
Excel 2004 are reduced by one day, e. g.

(April 2nd) 2.4.2006 becomes 1.4.2006
April 1st 1.4.2006 becomes 31.3.2006

works the same with any month, so it is not a fools' day bug )

I run 10.4.8 and the latest updates for MS Office, the problem has been
around for at lesat 1,5 years

anyone any idea?

 

[Niek Otten]

Are you sure the years are the same and not 4 years apart?

It seems to me that one system has the "normal" date system and the other one the 1904 system (standard on Mac, option on
Windows).
Tools>Options>Calculation tab.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| When I copy date fields from one sheet into another, date fields in
| Excel 2004 are reduced by one day, e. g.
|
| (April 2nd) 2.4.2006 becomes 1.4.2006
| April 1st 1.4.2006 becomes 31.3.2006
|
| works the same with any month, so it is not a fools' day bug )
|
| I run 10.4.8 and the latest updates for MS Office, the problem has been
| around for at lesat 1,5 years
|
| anyone any idea?
|

 

[JE McGimpsey]

When I copy date fields from one sheet into another, date fields in
Excel 2004 are reduced by one day, e. g.

(April 2nd) 2.4.2006 becomes 1.4.2006
April 1st 1.4.2006 becomes 31.3.2006

works the same with any month, so it is not a fools' day bug )

I suspect instead of 2.4.2006 becoming 1.4.2006, it's actually becoming
1.4.2002.

This is a problem with XL's original date system, the MacXL default 1904
system, being different than the WinXL defaultl 1900 date system.

The 4 years is obvious, but there's an additional change of 1 day
because the WinXL system incorrectly includes a spurious date - 29
February 1900.

The solution is either to use the same date system for each workbook
(Tools/Options/Calculation), or to add/subtract 1462 (the number of days
in 4 years + 1 day) to/from the dates in one workbook.

You can change multiple dates at once by entering 1462 in a cell. Copy
the cell. Select the cells to change, then choose Edit/Paste Special,
selecting the Values and Add (or Subtract) radio buttons.

 

[Andrei]

I suspect instead of 2.4.2006 becoming 1.4.2006, it's actually becoming
1.4.2002.

YESSS, you are right, sorry

This is a problem with XL's original date system, the MacXL default 1904
system, being different than the WinXL defaultl 1900 date system.

The 4 years is obvious, but there's an additional change of 1 day
because the WinXL system incorrectly includes a spurious date - 29
February 1900.

The solution is either to use the same date system for each workbook
(Tools/Options/Calculation), or to add/subtract 1462 (the number of days
in 4 years + 1 day) to/from the dates in one workbook.

You can change multiple dates at once by entering 1462 in a cell. Copy
the cell. Select the cells to change, then choose Edit/Paste Special,
selecting the Values and Add (or Subtract) radio buttons.

Thanks for the solutions, I already fixed the pbm on the Mac (changed
to 1904-dates), and now I know how to handle the interchange with Excel
Win