XL97: Copied and Pasted Dates Are Changed by 100 Years

ID: Q179584

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97 for Windows, if you copy a cell that contains a date, and then paste the date into a cell in another workbook, the date may unexpectedly change by 100 years. For example, when you paste the following sample dates, the following dates are pasted instead.

   Copied date   Pasted date
   -------------------------

   3/31/1920     3/31/2020
   3/31/2030     3/31/1930

CAUSE

This problem occurs if the following conditions are true:

After you copy the date, you close the workbook that contains this date.
```
    -and-
```
You then paste the date into another workbook.
```
    -and-
```
The copied date is less than or equal to 12/31/1929 or greater than or equal to 1/1/2030.
```
    -and-
```
The copied date is formatted so that two-year digits are displayed, not four.

WORKAROUND

To work around this problem, do not close the workbook that contains the copied date until after you paste the date into the other workbook.

If this problem has already occurred, you can manually reenter the incorrect dates. Or, if there are many incorrect dates, you can correct them by following these steps:

1. In an empty cell in the worksheet, enter the following number:

      36525

   Then, select the cell and click Copy on the Edit menu.

2. Select the cells that contain the incorrect dates.

3. On the Edit menu, click Paste Special. Click Values and click one of

   following option buttons.

      Click           To perform this action
      --------------------------------------------

      Add             Shift date up by 100 years
      Subtract        Shift date down by 100 years

4. Click OK.

The dates are shifted up or down by 100 years depending on the option button you clicked in step 3.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

In Microsoft Excel 97, if you copy a date, and then close the workbook that contains the copied date, the date in the Clipboard is converted into a text string that appears in the same form as the formatted date.

When you paste the text string into another workbook, Microsoft Excel determines that the text string resembles a date and converts it into a date. If the copied date was formatted to show only two year digits, Microsoft Excel 97 behaves as though you entered a date with only two year digits.

Example

To see an example of this behavior, follow these steps:

1. In a new workbook, enter the following date into cell A1:

      1/1/1920

   Note that although the formula bar contains 1/1/1920, the cell displays
   1/1/20.

2. Select cell A1. On the Edit menu, click Copy.

   The Clipboard contains the date 1/1/1920 and the cell formatting.

3. On the File menu, click Close. Click No when you are prompted to save

   changes to the file.

   Because you closed the workbook, the Clipboard contains a text string
   that is the same as the date that appeared in the cell:

      1/1/20

4. Create a new workbook. Select cell A1, and click Paste on the Edit

   menu.

The text string is pasted into the cell and converted into a date. Note that the date in the cell appears as 1/1/20. However, the date in the formula bar is 1/1/2020.

For more information about how Microsoft Excel handles two-digit years, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q164406
   TITLE     : XL: How Microsoft Excel Works with Two-Digit Year Numbers

For more information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site:

   http://www.microsoft.com/technet/topics/year2k/default.htm

Additional query words: XL97 y2k year2000 1919 1920 1929 1930 2019 2020 2029 2030 XL97 unexpectedly shift

Keywords          : kb2000 xlformat 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug

Last Reviewed: November 2, 1998