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

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: In this case, the value that is returned by the VLOOKUP formula in cell D1 may be #N/A.

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.

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.

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