Microsoft KB Archive/211640

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

Article ID: 211640

Article Last Modified on 10/6/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q211640





SYMPTOMS
In Microsoft Excel, 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 correct 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="moreinformation_section">

MORE INFORMATION
In Microsoft Excel, 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 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

If the date displays a four digit year, choose a number format that displays only two digit years, such as 3/14/98. 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.

<div class="references_section">