To count cells that contain a specific date in Excel, use COUNTIF with a date value as its criterion; to count every date in a day when cells may include times, use COUNTIFS with a start-of-day and next-day boundary. For example, with dates in B2:B100, an exact-date count is =COUNTIF(B2:B100,DATE(2025,3,8)).
What COUNTIF does with dates
COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range,criteria): the range is where Excel looks, and the criterion defines what qualifies. Dates work because Excel normally stores them as serial numbers, with each calendar day represented by a number. A displayed date such as March 8, 2025 is therefore a number with a date format applied, rather than merely a piece of text.
This numeric storage is why date criteria can be compared as equal to, greater than, or less than a date. It also explains a common surprise: a cell that looks like a date may actually contain text, and a cell that looks like a date may contain a hidden time as well. Formatting changes how a value appears; it does not necessarily change what the value is.
Use COUNTIF when one condition is enough, such as “count entries on March 8.” Use COUNTIFS when you need a range of dates or multiple conditions. Both functions are available in Microsoft Excel for Microsoft 365 on Windows.
Count an exact date
For a fixed date, Excel’s DATE function is a dependable way to supply the criterion. It creates a date from year, month, and day numbers, avoiding ambiguity between regional formats such as month/day/year and day/month/year.
With dates in B2:B100, the formula below counts cells whose stored value is exactly March 8, 2025:
=COUNTIF(B2:B100,DATE(2025,3,8))
If B2:B8 contain March 1, March 8, March 8, March 15, April 8, March 8 at 2:30 p.m., and a blank, this formula returns 2. The two plain March 8 entries match the date value at midnight. The March 8 entry with a time is a different, larger serial value, so it does not equal the date at midnight.
You can also point the criterion at a cell. With the dates in B2:B100 and the target date entered as a real Excel date in D2, use:
=COUNTIF(B2:B100,D2)
That approach is useful for reports where someone changes the date being counted without editing the formula. Make sure D2 contains an actual date value, not text that only looks like one. To enter a date unambiguously in a formula, DATE(year,month,day) is generally safer than typing a quoted date string.
Count dates in a range
To count all dates from a start date through an end date, including both endpoints, use COUNTIFS. It applies multiple conditions to the same range and counts a cell only when all conditions are true.
With dates in B2:B100, a start date in D2, and an end date in E2, use:
=COUNTIFS(B2:B100,">="&D2,B2:B100,"<"&E2+1)
This counts values on or after D2 and before the day after E2. The upper boundary is deliberately exclusive: it includes any time during the end date, such as 11:45 p.m., instead of counting only values exactly at midnight. D2 and E2 should be valid Excel dates, and the end date should not be earlier than the start date.
For a fixed March 2025 example, with dates in B2:B100, the following counts values from March 1 up to but not including April 1:
=COUNTIFS(B2:B100,">="&DATE(2025,3,1),B2:B100,"<"&DATE(2025,4,1))
In the sample data above, the result is 5: March 1, the two midnight March 8 entries, March 15, and March 8 at 2:30 p.m. April 8 and the blank are excluded. Using the first day of the next month as an exclusive limit is a reliable pattern for months of different lengths, including February and leap years.
Count dates in a month or a moving period
For a particular month, use the first day of that month as the lower limit and the first day of the next month as the upper limit. This avoids needing to calculate the month’s final day manually. The March example above follows that pattern.
To count dates in the current month automatically, with dates in B2:B100, use:
=COUNTIFS(B2:B100,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B2:B100,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))
TODAY() returns the current date, so the result updates when Excel recalculates on a later day or month. This is useful for a live monthly summary, but it may not be appropriate for a report that must preserve a fixed reporting period. In that case, put the month start and end in worksheet cells and reference them, so the criteria remain visible and editable.
The same boundary method works for a week, quarter, or custom reporting period: supply the start of the period as the inclusive lower bound and the first date after the period as the exclusive upper bound. For a calendar quarter, for instance, use April 1 and July 1 to count the second quarter. The technique is independent of how many days the period contains and includes timestamps throughout its final day.
Handle dates that include times
Excel date-time values combine a whole-number date with a fractional part for the time. Midnight has no fractional time portion; later times on that day have a fraction. Thus, the direct exact-date formula counts only values equal to midnight on the target date. This is the main reason a date count can be smaller than the number of rows that visually show that date.
To count every value that occurs on March 8, including times, with timestamps in B2:B100, use two conditions:
=COUNTIFS(B2:B100,">="&DATE(2025,3,8),B2:B100,"<"&DATE(2025,3,9))
This counts from midnight on March 8 up to, but not including, midnight on March 9. It includes any time on March 8 and excludes the next day. Avoid trying to create an end-of-day limit such as 11:59 p.m.; timestamps can include seconds or fractions of a second, and a strict next-day boundary avoids that gap.
The same formula structure works with a date stored in D2. With timestamps in B2:B100 and the target date in D2, use:
=COUNTIFS(B2:B100,">="&D2,B2:B100,"<"&D2+1)
This assumes D2 is a date at midnight, which is the usual result of entering a date without a time. If D2 itself includes a time, use a cell containing only the intended calendar date for the boundary.
Use criteria with cell references and operators
Criteria that include comparison operators must be written as text and joined to a date value with the ampersand operator. For example, ">="&D2 means “greater than or equal to the value in D2.” The operator is text; the referenced cell supplies the numeric date. The same construction works with <, >, and <=.
With dates in B2:B100 and a cutoff date in D2, this formula counts dates on or after the cutoff:
=COUNTIF(B2:B100,">="&D2)
For a count that changes when a user selects a different date or period, put the criteria in worksheet cells and reference those cells rather than embedding fixed dates. It makes the formula easier to maintain and lets the criteria be checked directly. If the worksheet is shared, label those input cells clearly and format them as dates.
For “either this date or that date,” a single COUNTIF has only one criterion. You can add two counts when the target dates are distinct. With dates in B2:B100 and dates to count in D2 and E2, use:
=COUNTIF(B2:B100,D2)+COUNTIF(B2:B100,E2)
This counts entries equal to either listed date. If D2 and E2 contain the same date, matching rows are counted twice; avoid duplicate criteria or use a different design if duplicates are possible. Also remember that this exact-match approach does not include timestamped entries unless their time portion matches the criterion.
Common date-counting problems
- The result is zero: Check whether the cells contain true Excel dates or text. A value imported from a CSV or copied from a website may look like a date but be stored as text. Changing its number format alone does not convert text to a numeric date. Excel’s error indicators, sorting behavior, and a test in a spare cell can help distinguish the two; a text value may need to be converted before counting.
- The count is too low: Look for hidden times in the source values. Use the two-boundary
COUNTIFSmethod to count a whole calendar day. A date display format may hide hours and minutes without removing them. - The date appears to shift or be interpreted differently: Excel’s interpretation of typed date text can depend on regional settings. Use
DATEwith numeric year, month, and day arguments, or use a real date cell as the criterion, rather than relying on an ambiguous text string. - The result changes unexpectedly: Check for formulas or data that recalculate, and distinguish fixed dates from formulas using
TODAY(). Also inspect the selected range: including a header is usually harmless for a date comparison, but omitting the final data row can make the count incomplete. - Values are in another workbook or column: Confirm that the formula refers to the actual date column and that the compared ranges in
COUNTIFShave the same dimensions. Each condition range should cover the same rows.
For troubleshooting, temporarily format a suspicious cell as General to see whether Excel stores a number, or inspect its formula bar. Do not assume that a display such as 3/8/2025 proves the underlying value is a date: formatting and stored value are separate things.
COUNTIF versus COUNTIFS
COUNTIF is concise for one condition: one exact date, one cutoff, or one other test. COUNTIFS is the better choice for a date interval because it can apply a lower and upper boundary together. It can also combine date conditions with other fields, such as counting records from a certain date range for one department or category.
For example, if dates are in B2:B100 and category names are in C2:C100, a date interval plus a category requires two date tests and one category test. Each condition range must line up row by row. The function counts only rows meeting every condition, which is an AND relationship. For alternatives such as one category or another, separate counts may be added, with care not to double-count rows that satisfy both alternatives.
Neither function changes or cleans the source data. If a workbook mixes text dates, numeric dates, and timestamps, standardizing the data first often makes the result easier to understand and maintain.
Frequently Asked Questions
Can COUNTIF count dates between two dates?
Not with two independent limits in one COUNTIF, because it accepts one criterion. Use COUNTIFS with a greater-than-or-equal start condition and a less-than condition for the day after the end date. That pattern includes the full end date even when the cells contain times.
Why does COUNTIF return zero for a date that is visibly in the range?
The displayed value may be text, or the criterion may not match the stored value. Check that the date column contains real Excel dates and that the target date is also a real date. If the source has times, count with a day-long interval instead of exact equality.
How do I count dates in the current month?
Use COUNTIFS with a lower bound equal to the first day of this month and an upper bound equal to the first day of next month. The DATE, YEAR, MONTH, and TODAY functions can create those boundaries automatically; the formula in the month section updates as time passes.
Does COUNTIF include dates with a time attached?
An exact-date criterion matches the date at midnight, not every timestamp on that calendar day. To include all times, count values greater than or equal to the date and less than the following date.
Can I use a date in another cell as the COUNTIF criterion?
Yes. Put a valid Excel date in a cell and use that cell as the criterion, such as =COUNTIF(B2:B100,D2). For an interval, use the cell references in the two COUNTIFS boundary conditions. This is generally easier to update than editing a fixed date inside the formula.
Related
- Company Cash Flow Template Excel: Build and Use It
- Commercial Invoice Template in Excel: What to Include
- Commission Calculation Software in Excel: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter Information in Excel: A Practical GuideTo filter information in Excel, select a cell in your data, choose Data > Filter, then use the…
- EDATE in Excel: How to Add Years to a Date CorrectlyExcel’s EDATE function adds months, not years. To add a whole number of years to a date in…
- Power BI XLOOKUP: DAX and Power Query Lookup OptionsPower BI does not have Excel’s XLOOKUP function in DAX. To match a value from another table, use…
- Excel INDEX MATCH Formula: How to Use It in Microsoft ExcelIn Microsoft Excel, combine INDEX and MATCH to find a value in one range and return the value…
- Minimum Sample Size Formula: How to Calculate ItThe minimum sample size depends on what you are estimating or testing. For a proportion in a large…
- Difference Between Numbers in Excel: Formulas & UsesTo find the difference between two numbers in Excel, subtract the second cell from the first: with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.