Microsoft KB Archive/211830

= Time values that are created by the fill feature may be incorrect in Excel =

Article ID: 211830

Article Last Modified on 2/8/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q211830



SYMPTOMS
When a formula references a cell that contains a time value in Microsoft Excel, the formula may return an unexpected value. For example, you may have the following data in Sheet1 of your workbook:   A1: 12:00 AM   B1: a   C1: 1:00 AM   D1: =VLOOKUP(C1,A1:B5,2,FALSE) A2: 12:30 AM  B2: b   A3:  1:00 AM   B3: c   A4:  1:30 AM   B4: d   A5:  2:00 AM   B5: e In this case, the value that is returned by the VLOOKUP formula in cell D1 may be #N/A.



CAUSE
You may experience this problem if the time values that your formula references were created by the fill feature in Excel. In the example that is mentioned in the "Symptoms" section, you would have manually typed the time values into cells A1 and A2, but used the fill feature to create the time values in cells A3, A4, and A5.



RESOLUTION
To resolve this issue, manually re-enter your time values. Alternatively, use the Precision as displayed option or the Set precision as displayed option, depending on the version of Excel that you are running.

To use the Precision as displayed option for the active workbook in Microsoft Office Excel 2003 and in earlier versions of Excel, follow these steps:  On the Tools menu, click Options. In the Options dialog box, click the Calculation tab. Click to select the Precision as displayed check box, and then click OK.

The following warning appears:

Data will permanently lose accuracy.

 Click OK.

To use the Set precision as displayed option for the active workbook in Microsoft Office Excel 2007, follow these steps:  Click the Microsoft Office Button, and then click Excel Options.</li> Select the Advanced category, and then click to select the Set precision as displayed check box under When calculating this workbook.

The following warning appears:

Data will permanently lose accuracy.

</li> Click OK.</li></ol>

NOTE: When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes any constant values on all worksheets in the workbook. If you later choose to calculate with full precision, the original underlying values cannot be restored.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Additional query words: XL2002 XL2000 XL2003 XL2007

Keywords: kbpending kbprb KB211830

-

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

© Microsoft Corporation. All rights reserved.