I copied a cell containing dates to a new workbook and the date changes by 1
day and the year changes from 2006 to 2003. For example I have 1/20/2006 and
the new workbook shows 1/19/2003. I looked at the cell formats and I see that
it's a date with an "*" in front of the format. Anyone knows why? How can I
fix it so that it copies and pastes correctly.

Thanks

Look at Tools/ Options/ Calculation/ 1904 date system.
Be careful changing this, as you'll get data confused as it goes to and fro
between the two systems. The 1900 system is the default in the Windows
version of Excel, but 1904 is the default for the Mac. Are you sure it's
changed by 3 years, not 4?
If you've got dates that are confused but you've got the right setting of
the date system, put the number 1462 into a spare cell, copy, and paste
special/ add or subtract as appropriate.
--
David Biddulph

"matelot" wrote in message
...
I copied a cell containing dates to a new workbook and the date changes by
1
day and the year changes from 2006 to 2003. For example I have 1/20/2006
and
the new workbook shows 1/19/2003. I looked at the cell formats and I see
that
it's a date with an "*" in front of the format. Anyone knows why? How can
I
fix it so that it copies and pastes correctly.

Thanks

You sure it is 3 years and 1 day and not 4 years and 1 day which is caused by
using two different date systems in separate workbooks?

ToolsOptionsCalculation Tab has a checkmark for 1904 date system which Mac
uses.

From JE McGimpsey(Mac user) posting........................

The workbooks have different base dates. The one you're copying from is
set to the WinXL default 1900 date system (base date 31 December 1899)
while the one you're copying to has the MacXL default 1904 date system
(base date 1/1/1904). Since XL stores dates as integer offsets from the
base date, you're seeing a 4 year + 1 day shift (the 1 day is an error
in the 1900 system which includes a phantom 29 February 1900).

You can change the date system of your destination workbook (date
systems are workbook specific) choosing Preferences/Calculation and
unchecking the 1904 date system checkbox.

As an alternative, you can enter 1462 in a cell, copy the cell, then
select the cells with the advanced dates. Choose Edit/Paste Special,
selecting the Values and Subtract radio buttons. Click OK.

End JE posting...............................


Gord Dibben MS Excel MVP



On Thu, 22 Feb 2007 17:44:15 -0800, matelot
wrote:

I copied a cell containing dates to a new workbook and the date changes by 1
day and the year changes from 2006 to 2003. For example I have 1/20/2006 and
the new workbook shows 1/19/2003. I looked at the cell formats and I see that
it's a date with an "*" in front of the format. Anyone knows why? How can I
fix it so that it copies and pastes correctly.

Thanks

Thanks to the both of you. It works when I place a check to the 1904.
Talk about interoperability between mac & pc. Why can't they start on the
same date? Thanks for the solution.

"Gord Dibben" wrote:

You sure it is 3 years and 1 day and not 4 years and 1 day which is caused by
using two different date systems in separate workbooks?

ToolsOptionsCalculation Tab has a checkmark for 1904 date system which Mac
uses.

From JE McGimpsey(Mac user) posting........................

The workbooks have different base dates. The one you're copying from is
set to the WinXL default 1900 date system (base date 31 December 1899)
while the one you're copying to has the MacXL default 1904 date system
(base date 1/1/1904). Since XL stores dates as integer offsets from the
base date, you're seeing a 4 year + 1 day shift (the 1 day is an error
in the 1900 system which includes a phantom 29 February 1900).

You can change the date system of your destination workbook (date
systems are workbook specific) choosing Preferences/Calculation and
unchecking the 1904 date system checkbox.

As an alternative, you can enter 1462 in a cell, copy the cell, then
select the cells with the advanced dates. Choose Edit/Paste Special,
selecting the Values and Subtract radio buttons. Click OK.

End JE posting...............................


Gord Dibben MS Excel MVP



On Thu, 22 Feb 2007 17:44:15 -0800, matelot
wrote:

I copied a cell containing dates to a new workbook and the date changes by 1
day and the year changes from 2006 to 2003. For example I have 1/20/2006 and
the new workbook shows 1/19/2003. I looked at the cell formats and I see that
it's a date with an "*" in front of the format. Anyone knows why? How can I
fix it so that it copies and pastes correctly.

Thanks