Excel Worksheet

[sassylassy]

When I copy a date from another file it always adds 1 day and 4 years to the date. Why does it do this? And how can I fix it?

 

[JE McGimpsey]

XL has two date systems, the Windows default 1900 date system, in which
the 0-base date is 31 December 1899, and the MacXL default 1904 date
system, in which the 0-base date is 1 January 1904. The systems differ
by 4 years and a day.

To resolve your issue, make sure that both workbooks have the same date
system selected (Tools/Options/Calculate, check or uncheck the 1904 date
system checkbox).

 

[Frank Kabel]

Hi
both files have different date base setting (1900 vs. 1904). goto
'Tools - Options - Calculation' and check the '1904 date settings. They
have to be identical for both workbooks

 

[Ken Wright]

http://cpearson.com/excel/datetime.htm

From Chip's site above:-

The Two Date Systems Of Excel
In the section above, it was said that the date portion of a serial date-time
represents the number of days since 1900-Jan-0. This is true, but Excel can
also treat the date portion of a serial date as the number of days since
1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for
compatibility with the Macintosh system. The date mode applies to all dates
within a workbook. You cannot mix and match modes within a workbook. Unless
absolutely required, you should never use the 1904-system. If you change
systems after you've entered some dates in a workbook, these dates will appear
to be off 4 years (because the serial dates haven't changed, only their display
representation has). Moreover, linking workbooks with different date systems
will cause problems.

Tools / Options / Calculation - Uncheck 1904 Date system

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

When I copy a date from another file it always adds 1 day and 4 years to the

date. Why does it do this? And how can I fix it?