Excel formula: Count cells between dates

Generic formula

=COUNTIFS(range,">="&date1,range,"<="&date2)

Summary

To count the number of cells that contain dates between two dates, you can use the COUNTIFS function. In the example shown, F6 contains this formula:

=COUNTIFS(dates,">="&DATE(E5,1,1),dates,"<="&DATE(E5,12,31))

This formula counts birthdays in the year 2000, that appear in the range C5:C11.

Note: this formula uses the named range "dates", C5:C11.

Explanation

The COUNTIFS function is built to count cells that meet multiple criteria. In this case, we need to provide two criteria: one criteria for the earlier date and one for the later date. We supply the named range dates (C5:C11) for both criteria.

To construct each date, we use the DATE function:

DATE(E5,1,1) // build first day of year
DATE(E5,12,31) // build last day of year

The DATE function makes it easy to build dates based on year, month, and day arguments that are either hard-coded or supplied as cell references. In the example, month and day are hard-coded, and we get year from column E.

Note: the operators ">=" and "<=" must be entered as text and surrounded by double quotes. This means we must use concatenation (&) to join each operator to each date.

Author

Dave Bruns

Related formulas

Count cells between two numbers

The COUNTIFS function is built to count cells that meet multiple criteria. In this case, because we supply the same range for two criteria, each cell in the range must meet both criteria in order to be counted. Using COUNTIF instead If you have an...

Highlight values between

When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as...

Highlight dates between

The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as...

Summary count by month with COUNTIFS

In this example, we have a list of 100 issues in Columns B to D. Each issue has a date and priority. We are also using the named range "dates" for C5:C104 and "priorities" for D5:D105. Starting in column F, we have a summary table that shows a total...

Related functions

Excel COUNTIFS Function

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,...

Excel DATE Function

The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.