Microsoft KB Archive/179584

= XL97: Copied and Pasted Dates Are Changed by 100 Years =

Article ID: 179584

Article Last Modified on 8/9/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q179584





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 from which you copied the date until after you paste the date into the destination workbook.

If this problem has already occurred, you can manually retype the incorrect dates. Or, if there are many incorrect dates, you can correct them by following these steps:  In an empty cell in the worksheet, enter the following number:

36525

Then, select the cell and click Copy on the Edit menu. Select the cells that contain the incorrect dates.  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  Click OK.</ol>

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

<div class="status_section">

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

<div class="moreinformation_section">

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:

<ol> 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.</li> Select cell A1. On the Edit menu, click Copy.

The Clipboard contains the date 1/1/1920 and the cell formatting.</li> 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

</li> Create a new workbook. Select cell A1, and click Paste on the Edit menu.</li></ol>

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:

302768How Microsoft Excel works with two-digit year numbers

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

Keywords: kbbug KB179584

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.