Microsoft KB Archive/48143

Setting Criteria for Date, Time Fields in an Excel Database PSS ID Number: Q48143 Article last modified on 02-09-1993 PSS database name: W_eXceL

2.x 3.00 4.00 | 2.20 2.21 3.00

WINDOWS | OS/2

Summary:

If you do not extract Date and Time fields correctly from within a database, the criteria you receive may not be correct. You may get every record or get nothing.

More Information:

The following are two examples of using Date and Time fields with a criteria range.

Example 1
To extract Date and Time fields from the database section of Microsoft Excel, set up your criteria to refer to an external cell that contains the date and time. The criteria cells must contain the following:

  The first cell contains the name of the field.   The second cell (below the first) contains the following: =“condition”&DATE/TIME_cell_reference 

The following items refers to the worksheet below:


 * Database=A2:B6
 * Criteria=C6:C7
 * DATE/TIME_cell_reference=C2
 * Extract Range=D2:E2
 * Extracted Values=D3:E4

Sample
A1: B1: C1: D1: E1: A2: Name B2: Time C2: 9:00 D2: Name E2: Time A3: Joe B3: 8:00 C3: D3: Joe E3: 8:00 A4: Bob B4: 9:00 C4: D4: Bob E4: 9:00 A5: Tom B5: 10:00 C5: D5: E5: A6: Mark B6: 11:00 C6: Time D6: E6: A7: B7: C7: =“<”&C2 E7: E7: A8: B8: C8: E8: E8:

Note that the criteria uses the exact field name. The criteria must begin with =&quot; followed by the condition, then &quot;& followed by the cell reference. For example:

=“<”&C2

Example 2
If you do not wish to enter the date or time in a separate cell, you can build your criteria formula using computed criteria. Considering the example database above, the criteria range in cells C6 and C7 would appear as follows:

C6: C7: =b3<TIMEVALUE(“09:00:00”)

Note that the contents of cell C6 must be either blank or an entry that does not correspond with any field name in the database.

For more information on using computed criteria with a Microsoft Excel database see:

“Microsoft Excel Reference,” version 2.XX, pages 180-181

“Microsoft Excel User’s Guide,” version 3.00, pages 374-375

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00

Copyright Microsoft Corporation 1993.