Excel COUNTIF Date in Cell? Formula & Examples

Formulas & functions · Updated November 22, 2026

To count cells containing a specific date in Excel, use =COUNTIF(range, date) where date is a date entered with the DATE function, a cell reference, or a date serial number. For example, =COUNTIF(A2:A10, DATE(2025,3,15)) counts how many cells in A2:A10 equal March 15, 2025. If you need to count dates that fall within a range (e.g., all dates in March), use COUNTIFS with two criteria.

How COUNTIF Handles Dates in Excel

Excel stores dates as serial numbers—January 1, 1900 is 1, and each day increments by 1. So March 15, 2025 is stored as 45731. When you type a date into a formula like =COUNTIF(A2:A10, "3/15/2025"), Excel usually interprets the text as a date, but this can fail if your system’s date settings differ. The safest approach is to use the DATE function: =COUNTIF(A2:A10, DATE(2025,3,15))—this avoids any regional date format ambiguity.

Also note that a cell containing a date and time (e.g., 3/15/2025 8:00 AM) will not be counted by =COUNTIF(A2:A10, DATE(2025,3,15)) because the time portion makes the serial number larger than the date-only value. To count a date regardless of time, use =COUNTIF(A2:A10, ">="&DATE(2025,3,15)) - COUNTIF(A2:A10, ">="&DATE(2025,3,16)) or use SUMPRODUCT with INT.

Counting Dates in a Range (Between Two Dates)

Use COUNTIFS when you need to count dates that fall within a start and end date. With dates in A2:A20, to count dates from March 1, 2025 through March 31, 2025:

=COUNTIFS(A2:A20, ">="&DATE(2025,3,1), A2:A20, "<="&DATE(2025,3,31))

This returns the number of cells in A2:A20 that are greater than or equal to March 1 and less than or equal to March 31. The ampersand (&) concatenates the comparison operator with the date serial number. You can replace the DATE functions with cell references, e.g., =COUNTIFS(A2:A20, ">="&B1, A2:A20, "<="&B2) where B1 and B2 contain the start and end dates.

Counting Dates with a Specific Year, Month, or Day

To count all dates in a given year, use SUMPRODUCT with the YEAR function. For example, to count dates in A2:A20 that fall in 2025:

=SUMPRODUCT(--(YEAR(A2:A20)=2025))

The double unary (--) converts TRUE/FALSE to 1/0 so SUMPRODUCT can add them. For a specific month (e.g., March 2025):

=SUMPRODUCT(--(MONTH(A2:A20)=3), --(YEAR(A2:A20)=2025))

For a specific day of the week (e.g., all Mondays), use WEEKDAY: =SUMPRODUCT(--(WEEKDAY(A2:A20)=2)) (Sunday=1, Monday=2, etc.). These formulas work in Excel 365, Excel 2021, and earlier versions.

Common Problems and Fixes

  • Dates stored as text: If your cells contain text that looks like a date, COUNTIF won’t match. Convert text to real dates using DATEVALUE in a helper column, or use -- to coerce. For example, with text dates in A2:A10, use =SUMPRODUCT(--(DATEVALUE(A2:A10)=DATE(2025,3,15))).
  • Blank cells or errors: COUNTIF ignores blanks, but if a cell contains an error like #N/A, it will not be counted (unless you use COUNTIF with a criteria that matches errors). To avoid errors, wrap ranges in IFERROR if needed.
  • Regional date settings: Using DATE(2025,3,15) always works regardless of whether your system uses mm/dd/yyyy or dd/mm/yyyy. Avoid typing dates as strings like "3/15/2025" because Excel might interpret it as March 15 or March 15 depending on locale.
  • Counting dates in a pivot table: If you need to count dates by month or year, a pivot table with the date field grouped is often easier than a formula.

COUNTIF vs. COUNTIFS vs. SUMPRODUCT

Use COUNTIF for a single condition. Use COUNTIFS for multiple conditions—it’s faster and clearer than nested COUNTIFs. Use SUMPRODUCT when you need to calculate based on functions like YEAR, MONTH, or WEEKDAY, or when you need to count with array logic. For example, to count dates that are both in 2025 and in March, COUNTIFS can do it: =COUNTIFS(A2:A20, ">="&DATE(2025,3,1), A2:A20, "<="&DATE(2025,3,31)). But to count dates that are in Q1 (Jan-Mar) of any year, you need SUMPRODUCT.

Excel for Mac and Web Differences

The formulas above work identically in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The only difference is keyboard shortcuts: on Mac, press Cmd instead of Ctrl for actions like copy/paste. The DATE function and comparison operators are the same. If you’re using Excel for the web, you can enter the same formulas; there’s no difference in syntax.

Frequently Asked Questions

Why does COUNTIF not count my dates?

Most often because the cells contain date-time values or because the dates are stored as text. Check the cell format: if it’s text, the number is left-aligned. Use =ISNUMBER(A2) to test. If it returns FALSE, convert to a real date with DATEVALUE.

How do I count dates that are today?

Use =COUNTIF(A2:A20, TODAY()). This counts cells equal to today’s date. Note that if cells contain times, they won’t match; use =COUNTIFS(A2:A20, ">="&TODAY(), A2:A20, "<"&TODAY()+1) to count all timestamps today.

Can I count dates older than 30 days?

Yes: =COUNTIF(A2:A20, "<"&TODAY()-30) counts dates more than 30 days before today. For dates within the last 30 days, use =COUNTIF(A2:A20, ">="&TODAY()-30).

How do I count dates by month name (e.g., March)?

Use =SUMPRODUCT(--(TEXT(A2:A20,"mmm")="Mar")) but be careful with locale—"Mar" may vary. Safer: =SUMPRODUCT(--(MONTH(A2:A20)=3)) to count all March dates regardless of year. To count March 2025 specifically, use the COUNTIFS example above.

Does COUNTIF work with date ranges in Google Sheets?

Yes, the syntax is identical in Google Sheets. The only difference is that Google Sheets may require ARRAYFORMULA for some array operations, but COUNTIF and COUNTIFS work the same. For SUMPRODUCT, Google Sheets also supports it, but you might need to use ARRAYFORMULA in some cases.

What if I want to count blank date cells?

Use =COUNTBLANK(A2:A20) to count truly blank cells. If you have formulas that return empty strings, COUNTBLANK won’t count them; use =SUMPRODUCT(--(A2:A20="")) instead.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.