Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q211830

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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:
  1. On the Tools menu, click Options.
  2. In the Options dialog box, click the Calculation tab.
  3. Click to select the Precision as displayed check box, and then click OK.

    The following warning appears:
    Data will permanently lose accuracy.
  4. Click OK.
To use the Set precision as displayed option for the active workbook in Microsoft Office Excel 2007, follow these steps:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. 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.
  3. Click OK.
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.

↑ Back to the top


Status

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

↑ Back to the top


Keywords: KB211830, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 211830
Revision : 6
Created on : 2/8/2007
Published on : 2/8/2007
Exists online : False
Views : 370