XL97: Copied and Pasted Dates Are Changed by 100 Years

• 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.

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

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.

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

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

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

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