Excel: Alternate Formula Entry Affects Date Criteria

• 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: =">"&DATEVALUE("2/2/91")
  

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 "/" 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

1. Enter the following data on a worksheet:

        A1: Field    B1: Field       C1: Field
        A2: 1/1/91   B2: >"2/1/91"   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).
   
   

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 327-343

"Switching to Microsoft Excel from Lotus 1-2-3," version 4.0, page 9

"Microsoft Excel Function Reference," version 4.0, pages 94-95