COUNTIF With Dates: Before, After and Between

A COUNTIF date formula in Excel counts the dates before, after or between the days you choose. The key is to build the date with DATE and join it to the operator with &, as in =COUNTIF(B2:B8,">="&DATE(2024,3,1)). This guide shows each kind of date count on one small ticket log, plus whole months, dates in cells, today's date and the reasons a date count returns 0.

How dates work in COUNTIF criteria#

Excel stores every date as a number: the count of days since 1 January 1900. 1 March 2024 is the number 45352, shown in a date format. So counting dates is really counting numbers, and the same operators work: >, <, >=, <= and <>.

The safest way to write a date in a criteria is the DATE function, DATE(year, month, day), joined to the operator with &:

">="&DATE(2024,3,1)

You may see criteria like ">=3/1/2024". Excel tries to read that text as a date using your computer's regional settings, so 3/1 is 1 March in the US but 3 January in the UK. DATE always means the same day everywhere.

Every example uses this ticket log in A1:B8:

How to count dates before or after a date#

Use COUNTIF with one condition. These are the common forms:

Watch the difference between > and >=. "After 15 March" with > leaves out T-204, which was opened on 15 March. "On or before 15 March" with <= keeps it. Together they cover all seven tickets: 3 + 4 = 7. If COUNTIF itself is new, start with how to use COUNTIF.

How to count dates between two dates#

COUNTIF takes one condition, so for a date range use COUNTIFS with the same column twice: one condition for the start and one for the end.

=COUNTIFS(B2:B8,">="&DATE(2024,3,1),B2:B8,"<="&DATE(2024,3,31))

The result is 4: T-202, T-203, T-204 and T-205. A date is counted only when it passes both tests. The COUNTIFS function guide lists the arguments, and COUNTIF vs COUNTIFS explains the difference between the two functions. The same between pattern works for any numbers, as shown in COUNTIF between two numbers.

How to count dates in a month#

To count a whole month, count from its first day to its last. EOMONTH finds the last day for you, so you don't need to remember whether a month has 30 or 31 days. With 1 Mar 2024 in E1:

=COUNTIFS(B2:B8,">="&E1,B2:B8,"<="&EOMONTH(E1,0))

EOMONTH(E1,0) returns 31 March 2024, the end of the month that E1 is in. The result is 4. Change E1 to 1 Apr 2024 and the count becomes 2 (T-206 and T-207).

To build a monthly summary, list the first day of each month down column E, put this formula next to the first one with the range locked as $B$2:$B$8, and fill down (Windows: Ctrl+D; Mac: ⌘D). To total amounts by month instead of counting rows, see how to sum by date range or month.

How to use dates from cells#

Put the start and end dates in cells so anyone can change the period. With the start date in E1 and the end date in F1:

=COUNTIFS(B2:B8,">="&E1,B2:B8,"<="&F1)

The operator stays inside quotes and the cell reference stays outside, joined with &. Writing ">=E1" is the most common mistake: inside quotes, E1 is just text, so the formula returns 0.

How to count dates relative to today#

TODAY returns the current date, so you can join it to an operator just like DATE:

These results change every day, which is the point for a tracker of overdue tasks. For a fixed report, use DATE instead so the numbers stay the same when someone opens the file next month.

Why COUNTIF with dates returns 0 or the wrong count#

Work through these causes:

1. Cell reference inside the quotes. Use ">="&E1, not ">=E1".
2. Dates stored as text. Imported dates are sometimes text, which COUNTIF can't compare as numbers. Real dates sit on the right of the cell by default. Fix them with Data > Text to Columns or DATEVALUE.
3. Dates with times. A value like 31 Mar 2024 14:30 is bigger than 31 March at midnight, so "<="&DATE(2024,3,31) misses it. Use "<"&DATE(2024,4,1) as the end condition instead.
4. Text dates in the criteria. ">=1/3/2024" may be read as 3 January instead of 1 March. Use DATE.
5. Blank cells. COUNTIF with "<"&DATE(...) doesn't count blanks, but a criteria like "<>"&DATE(...) does. To count only filled cells, see COUNTIF not blank.

How to count dates with a second condition#

COUNTIFS can mix date conditions with conditions on other columns. Say column C holds each ticket's status:

To count the March tickets that are still open, add a third pair:

=COUNTIFS(B2:B8,">="&DATE(2024,3,1),B2:B8,"<="&DATE(2024,3,31),C2:C8,"Open")

The result is 2 (T-203 and T-205). A row counts only when every condition is TRUE.

Practise it#

Dates in criteria are where many people get stuck, because of the & join and the hidden times. Practise dates in criteria in a live spreadsheet, and brush up on the basics with the COUNTIF lesson. Each lesson checks your formula instantly. Or count the March tickets in the exercise on this page.