Trouble with Dates between 2 seperate worksheets
 

In Excel 2000, I am having trouble with dates retaining when copying a
worksheet from one workbook to another.

For example, in one cell, we have a date of April 8, 2005 and when I copy
the worksheet to another workbook, is changes to April 9, 2009. (it adds 4
years and 1 day)

I have tried several formatting changes - with no luck. HELP PLEASE IT IS
MAKING ME CRAZY!!


--
Julie

Saved from a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

Julie wrote:

In Excel 2000, I am having trouble with dates retaining when copying a
worksheet from one workbook to another.

For example, in one cell, we have a date of April 8, 2005 and when I copy
the worksheet to another workbook, is changes to April 9, 2009. (it adds 4
years and 1 day)

I have tried several formatting changes - with no luck. HELP PLEASE IT IS
MAKING ME CRAZY!!

--
Julie

--

Dave Peterson

The workbooks are using diffferent date systems under
toolsoptionscalculation,
one is using default windows the other Mac 1904 date syatems thus the 4
years and on day,
you can put 1462 in an empty cell, copy it and select the dates you want to
convert and
paste special and either subtract or add depending on from which workbook
you arte coming,
note that if you set both workbooks to the same you need to convert alreay
existing dates on one
or the other

--

Regards,

Peo Sjoblom


"Julie" wrote in message
...
In Excel 2000, I am having trouble with dates retaining when copying a
worksheet from one workbook to another.

For example, in one cell, we have a date of April 8, 2005 and when I copy
the worksheet to another workbook, is changes to April 9, 2009. (it adds 4
years and 1 day)

I have tried several formatting changes - with no luck. HELP PLEASE IT IS
MAKING ME CRAZY!!


--
Julie

On Fri, 4 Mar 2005 11:29:39 -0800, "Julie"
wrote:

In Excel 2000, I am having trouble with dates retaining when copying a
worksheet from one workbook to another.

For example, in one cell, we have a date of April 8, 2005 and when I copy
the worksheet to another workbook, is changes to April 9, 2009. (it adds 4
years and 1 day)

I have tried several formatting changes - with no luck. HELP PLEASE IT IS
MAKING ME CRAZY!!

The problem is that Excel has two date systems. One is based on the PC
date system (starting 1/1/1900), the other on the Macintosh date
system (starting in 1904).

One of your workbooks is set to use one system, the other, the other
system. Thus the same value = a different date.

You can set the system in use through the Tools - Options -
Calculation tab. It's workbook specific, so just set both workbooks to
the same system.




---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *