Yes, you can use COUNTIF to count cells that contain a date, but the exact formula depends on whether the cells contain real Excel dates (serial numbers) or text that looks like a date. For real dates, the simplest approach is =COUNTIF(range, date) or =COUNTIF(range, ">="&DATE(2024,1,1)) for a range of dates. If the dates are stored as text, you need wildcards: =COUNTIF(range, "*1/1/2024*"). This article explains each scenario in detail, including how to avoid common pitfalls like counting hidden time values or text-formatted dates.
Understanding How Excel Stores Dates
Before writing any COUNTIF formula, it helps to know that Excel stores dates as serial numbers. January 1, 1900 is serial number 1, January 1, 2024 is serial number 45292, and so on. When you see a date in a cell, Excel is actually displaying a formatted number. This matters because COUNTIF compares values, not displayed text. A cell containing the date 1/1/2024 is really the number 45292, so you can count it by referencing that number or using the DATE function.
If a cell contains text like “1/1/2024” (perhaps imported from another program), it is not a real date. COUNTIF will treat it as text, and wildcard characters become necessary. Knowing which type you have is the first step to a correct formula.
Counting Cells with a Specific Date
To count cells that equal a specific date, use the DATE function inside COUNTIF. For example, with dates in A2:A100, the formula:
=COUNTIF(A2:A100, DATE(2024,1,1))
This counts every cell in A2:A100 that contains the serial number for January 1, 2024. The DATE function ensures Excel interprets the date correctly, regardless of your system’s date settings. If you type the date directly as a string, like =COUNTIF(A2:A100, "1/1/2024"), Excel usually converts it to a date, but using DATE is safer.
For a date stored in another cell, say B1, use a cell reference:
=COUNTIF(A2:A100, B1)
This works because COUNTIF compares the serial numbers.
Counting Cells with a Date in a Range (Greater Than, Less Than)
Often you want to count dates after a certain day, before a certain day, or within a period. COUNTIF supports comparison operators, but you must concatenate them with the date using an ampersand (&).
With dates in A2:A100, to count dates on or after January 1, 2024:
=COUNTIF(A2:A100, ">="&DATE(2024,1,1))
To count dates before January 1, 2024:
=COUNTIF(A2:A100, "<"&DATE(2024,1,1))
For a date in cell B1, use =COUNTIF(A2:A100, ">="&B1). The ampersand joins the operator (as text) with the date serial number. Without the ampersand, Excel would treat the whole string as text and return zero.
To count dates between two dates, you need two COUNTIFs or use COUNTIFS. For example, dates in January 2024:
=COUNTIFS(A2:A100, ">="&DATE(2024,1,1), A2:A100, "<="&DATE(2024,1,31))
This counts cells that meet both conditions.
Counting Cells That Contain a Date (Any Date)
If you want to count how many cells contain any date at all, COUNTIF alone cannot do it directly because there is no "is date" criterion. One common workaround is to use SUMPRODUCT with the ISNUMBER function, since dates are numbers:
=SUMPRODUCT(--ISNUMBER(A2:A100))
This counts every cell in A2:A100 that contains a number. However, it also counts plain numbers, not just dates. To restrict to actual dates, you can check that the number is greater than a known early date (e.g., 1/1/1900) and less than a future date, but that still includes any number in that range. For most practical purposes, if your column contains only dates or blanks, =SUMPRODUCT(--ISNUMBER(A2:A100)) works fine.
Another approach is to use COUNTIF with wildcards if the dates are text, but for real dates, wildcards do not work because the underlying value is numeric.
Handling Text Dates and Wildcards
When dates are stored as text (e.g., "1/1/2024" as a string), COUNTIF treats them as text. To count cells that contain a specific text date anywhere within the cell, use wildcards. For example, with text dates in A2:A100:
=COUNTIF(A2:A100, "*1/1/2024*")
The asterisks match any characters before or after. This counts cells that contain that exact text sequence. Be careful: this will also match "11/1/2024" because it contains "1/1/2024". To avoid that, you could use a more specific pattern, but text dates are messy. The best solution is to convert text dates to real dates using the DATEVALUE function or Text to Columns (Data > Text to Columns > Delimited > Next > Next > Date).
To count text dates that fall in a specific month, you can use wildcards for the month part, but it gets complicated. For example, to count all text dates in January 2024, you could use =COUNTIF(A2:A100, "*/1/2024") if the format is m/d/yyyy, but this fails for dates like 1/10/2024. A better approach is to convert to real dates first.
Common Problems and Solutions
Several issues trip up users when counting dates:
- Formula returns 0: Check if the cells contain text dates. Try
=ISTEXT(A2)on a sample cell. If TRUE, use wildcards or convert to real dates. - Formula counts too many: If you used
=COUNTIF(A2:A100, "*1/1/2024*"), it might match dates like 11/1/2024. Use exact match with real dates instead. - Time values interfere: A cell with date and time (e.g., 1/1/2024 6:00 PM) has a serial number with a decimal.
=COUNTIF(A2:A100, DATE(2024,1,1))will not count it because the time makes it greater than the date alone. To count all entries on that date regardless of time, use=COUNTIF(A2:A100, ">="&DATE(2024,1,1)) - COUNTIF(A2:A100, ">="&DATE(2024,1,2))or use SUMPRODUCT with INT. - Blank cells counted: If you use
=COUNTIF(A2:A100, "*")to count text, blanks are not counted, but cells with spaces are. Use=COUNTIF(A2:A100, "?*")to count non-empty text cells.
Using COUNTIFS for Multiple Date Conditions
COUNTIFS extends COUNTIF to multiple criteria. It is the best way to count dates within a range. For example, to count orders placed between March 1, 2024 and March 31, 2024, with dates in A2:A100:
=COUNTIFS(A2:A100, ">="&DATE(2024,3,1), A2:A100, "<="&DATE(2024,3,31))
You can also add criteria from other columns. For instance, to count orders in March 2024 with a status of "Shipped" in B2:B100:
=COUNTIFS(A2:A100, ">="&DATE(2024,3,1), A2:A100, "<="&DATE(2024,3,31), B2:B100, "Shipped")
This is more efficient than using multiple COUNTIF formulas.
Frequently Asked Questions
Why does my COUNTIF with a date return 0?
Most likely the cells contain text that looks like a date, not a real Excel date. Use =ISNUMBER(A2) to check. If FALSE, convert the text to a date using DATEVALUE or Text to Columns.
Can I use wildcards with real dates?
No, wildcards only work with text. Real dates are numbers, so =COUNTIF(A2:A100, "*1/1/2024*") will return 0. Use the DATE function or a cell reference instead.
How do I count dates that are in a specific month, ignoring the day?
Use COUNTIFS with two conditions: greater than or equal to the first day of the month, and less than the first day of the next month. For example, for April 2024: =COUNTIFS(A2:A100, ">="&DATE(2024,4,1), A2:A100, "<"&DATE(2024,5,1)).
Does COUNTIF count cells with date and time together?
Only if you use a comparison that includes the time portion. A cell with 1/1/2024 12:00 PM is not equal to DATE(2024,1,1) because the serial number includes a fraction. Use the range method or SUMPRODUCT with INT.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet a single condition. COUNTIFS counts cells that meet multiple conditions across one or more ranges. For date ranges, COUNTIFS is usually easier.
Can I count dates that are blank or non-blank?
Yes. To count non-empty cells (which may include dates), use =COUNTA(A2:A100). To count empty cells, use =COUNTBLANK(A2:A100). These work regardless of whether the dates are text or numbers.
Alternatives to COUNTIF for Dates
While COUNTIF is the standard tool, other functions can be more flexible:
- SUMPRODUCT: Allows array calculations, e.g.,
=SUMPRODUCT((A2:A100>=DATE(2024,1,1))*(A2:A100<=DATE(2024,1,31))). Useful when you need to count based on derived conditions. - FILTER (Excel 365): Returns an array of matching values, which you can wrap in COUNTA, e.g.,
=COUNTA(FILTER(A2:A100, (A2:A100>=DATE(2024,1,1))*(A2:A100<=DATE(2024,1,31)))). This is more readable but requires Microsoft 365. - PivotTable: For large datasets, a PivotTable can group dates by month or year and count them interactively, without formulas.
Choose the method that fits your Excel version and the complexity of your criteria.
Related
- Excel and Access Training: What You Actually Need
- Convert KMZ to Excel: 4 Ways That Actually Work
- DepEd Class Attendance Sheet Template Excel: Free Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function From the Right: Formulas That WorkNo: Excel’s MID function always counts characters from left to right. To take a fixed number of characters…
- Revit IF Formula Examples for Family ParametersIn a Revit family, an IF formula tests a condition and returns one value when it is true…
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
- Excel Filter Out Text: Keep Numbers or Hide WordsTo filter text out of a mixed Excel column and keep numeric rows, use a helper column with…
- TVM Solver in Excel: Functions and Goal SeekIf you mean a TVM (time value of money) solver, yes: Excel can calculate present value, future value,…
- Excel Highlight Duplicates With Unique Colors?Yes—but Excel’s built-in duplicate-highlighting command applies one format to every duplicate, not a different color to each repeated…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.