Problem with copying dates

[Xt]

Some data was sent to me in Excel 2003. A column contained dates, a
typical one being Tuesday, 14 July 2009. I copied this to another
2007 workbook. It appeared as Wednesday, 13 July 2005. Both these
dates gave 38546 when put into general format.
If I type 14 jul 2009 into the new workbook and change the format to
general is becomes 40008.

Which is right? Any suggestions?

Thanks

xt

 

[Pete_UK]

Your Excel 2003 is using the 1904 date system (which is commonly used
in Mac versions), whereas your 2007 version is using the 1900 date
system (more common on PCs). You get a difference of 4 years and a day
if you move from one system to the other. Whichever system you decide
to use, ensure that it is consistent to both versions.

Hope this helps.

Pete

 

[Joe User]

A column contained dates, a typical one being
Tuesday, 14 July 2009. I copied this to another
2007 workbook. It appeared as Wednesday, 13 July 2005.
Both these dates gave 38546 when put into general format.

Some people think it's clever to set the Calculation option "1904 date
system", typically to allow them to display negative time.

This is the trouble they get their unwary advisees into.

You might want to deselect the "1904 date system" option in one workbook or
the other. I cannot help you with Excel 2007.

But that might have consequences that you will have to deal with.

In the future, if anyone suggests using the "1904 date system" for anything
but importing Mac Excel files, just ignore them and find an more suitable
way to solve the problem (probably negative dates).


----- original message -----

 

[Joe User]

Minor errata....

In the future, if anyone suggests using the
"1904 date system" [option] for anything but
importing Mac Excel files [....]

Actually, I think it's "export MSWin Excel files to Macs". To be safe, I
should be vague and say, "for transferring Excel files between MSWin and Mac
systems" ;-).


----- original message -----

 

[Xt]

Minor errata....

In the future, if anyone suggests using the
"1904 date system" [option] for anything but
importing Mac Excel files [....]

Actually, I think it's "export MSWin Excel files to Macs".  To be safe,I
should be vague and say, "for transferring Excel files between MSWin and Mac
systems" ;-).

----- original message -----

"Joe User" <joeu2004> wrote in message

Some people think it's clever to set the Calculation option "1904 date
system", typically to allow them to display negative time.

This is the trouble they get their unwary advisees into.

You might want to deselect the "1904 date system" option in one workbook
or the other.  I cannot help you with Excel 2007.

But that might have consequences that you will have to deal with.

In the future, if anyone suggests using the "1904 date system" for
anything but importing Mac Excel files, just ignore them and find an more
suitable way to solve the problem (probably negative dates).

----- original message -----

- Show quoted text -

Thanks for that. A trap I never knew existed. Cheers. xt