XL2000: Copied and Pasted Dates Are Changed by 100 Years

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.

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.

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:

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 that you clicked in step 3.

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:

1. 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.
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 additional information about how Microsoft Excel handles two-digit years, click the article number below to view the article in the Microsoft Knowledge Base: