date-1 day when copying

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrei - 28 Nov 2006 19:15 GMT

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?

Reply to this Message

Niek Otten - 28 Nov 2006 20:15 GMT

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.

Signature

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.
[quoted text clipped - 8 lines]
|
| anyone any idea?

Reply to this Message

JE McGimpsey - 28 Nov 2006 20:57 GMT

> When I copy date fields from one sheet into another, date fields in
> Excel 2004 are reduced by one day, e. g.
[quoted text clipped - 3 lines]
>
> 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.

Reply to this Message

Andrei - 29 Nov 2006 07:21 GMT

JE McGimpsey schrieb:

> > When I copy date fields from one sheet into another, date fields in
> > Excel 2004 are reduced by one day, e. g.
[quoted text clipped - 6 lines]
> 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.
[quoted text clipped - 10 lines]
> 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

Reply to this Message

Mac Forum / Applications / Excel / November 2006

date-1 day when copying