Microsoft KB Archive/89732

{| = Excel: Alternate Formula Entry Affects Date Criteria =
 * width="100%"|

Last reviewed: November 10, 1994

Article ID: Q89732 The information in this article applies to:
 * Microsoft Excel for Windows, version 4.0

SUMMARY
In version 4.0 of Microsoft Excel for Windows, if you select the Alternate Formula Entry (AFE) option, dates used as database criteria may not function as expected. Operations such as Find or Extract performed on your database may not produce the expected results.

WORKAROUNDS
To workaround this behavior, do either of the following:

 Turn Alternate Formula Entry off by choosing Calculation from the Options menu and clearing the Alternate Formula Entry check box. -or-  Use the DATEVALUE function for the date value entered in your criteria range. For example, you could use the following criteria: B1: Field B2: =&quot;>&quot;&DATEVALUE(&quot;2/2/91&quot;) 

MORE INFORMATION
Alternate Formula Entry provides for data entry in Excel that is similar to the way data is entered in Lotus 1-2-3. With Alternate Formula Entry selected, dates entered using the slash character &quot;/&quot; are evaluated as formulas by Find and Extract. For example, with Alternate Formula Entry selected, 1/2/92 is equivalent to the product 1 divided by 2, divided by 92, or .00543, instead of the serial number (date value) 33605.

Steps to Reproduce Behavior
  Enter the following data on a worksheet: A1: Field   B1: Field       C1: Field A2: 1/1/91  B2: >&quot;2/1/91&quot;   C2: A3: 2/1/91  B3:             C3: A4: 3/1/91  B4:             C4: A5: 4/1/91  B5:             C5:

2. Select the range A1:A5 and choose Set Database from the Data menu to define this range as a Database.

3. Select the range B1:B2 and choose Set Criteria from the Data menu to define this range as the Criteria range.

4. Select cell C1 and choose Set Extract from the Data menu to define C1 as the Extract range.

5. From the Options menu, choose Calculation.

6. In the Calculation Options dialog box, clear the Alternate Formula Entry option in the Sheet Options group then choose OK.

7. From the Data menu, choose Extract.

With Alternate Formula Entry disabled, two records are retrieved.

8. From the Options menu, choose Calculation.

9. In the Calculation Options dialog box, select the Alternate Formula Entry option in the Sheet Options group then choose OK.

10. From the Data menu, choose Extract.

With Alternate Formula Entry selected, no records are retrieved (that  is, the Extract range is empty). 