Many records include a date stamp of some sort. Usually the date marks an event or the input date. Either way, counting the number of records that fall between two dates is easy using the COUNTIFS() function (which isn’t available in Excel 2003 or earlier). To illustrate, the following sheet has a number of date-related records. The formula in cell B3
=COUNTIFS(A5:A12,">="&B1,A5:A12,"<="&B2)returns the number of dates (records) in A5:A12 that fall between the dates in B1 and B2.
The COUNTIFS() function uses the following syntax to specify multiple criteria to determine which values in a range to count:
COUNTIFS(countrange1, criteria1, [countrange2, criteria2]...)In this example, the count range is the same for each criteria set, the dates in column A. The first criteria set counts all the dates in A5:A12 that are equal to or fall after the date value in B1. The second criteria set counts dates that are equal to or occur before the date value in B2.
The only stipulation with this particular setup is that the value in B1 must be less than the value in B2. If you enter them backward, the function will return 0, instead of an error. To apply a conditional format to alert your users, do the following:
- Select B2.
- Click the Home tab and then click Conditional Formatting in the Styles group.
- Choose New Rule.
- Choose the Format Only Cells That Contain option.
- Retain Cell Value in the first dropdown-that’s what you want.
- Choose Less Than from the second dropdown.
- Enter =$B$1 in the final control.
- Click Format.
- Click the Fill tab, choose a background color, and click OK.
- Click OK again.
The format won’t stop a user from making the mistake, but will alert them that the value they’ve chosen isn’t appropriate.
