Excel: Select Records for Last 30 Days Criteria Example Wrong

Last reviewed: November 2, 1994
Article ID: Q60911

SUMMARY

The formula =">=NOW()-30" on page 170 of the "Microsoft Excel Reference" version 2.2 manual does not select all records for the last 30 days in a database.

To correctly perform this function, a computed criteria must be used, as in the following example (where B2 is a reference to the date field in the first record in the database):

   =B2>=NOW()-30

To set up the computed criteria, place this formula at the end of the criteria range. Do not include a fieldname as the heading for the computed criteria; either include a descriptive text string, or leave the cell above the formula blank.

For example, if the criteria is located in cells G1:G2, enter the computed criteria in cell H2. Select cells G1:H2 and choose Set Criteria from the Data menu.

   +---+----------+---------------+
   |   |    G     |       H       |
   +---+----------+---------------+
   | 1 |  Name    |               |
   +---+----------+---------------+
   | 2 |          | =B2>=NOW()-30 |
   +---+----------+---------------+

For more information on computed criteria, see pages 171-172 of the "Microsoft Excel Reference" version 2.2 manual.

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.