Using COUNTIF in Excel With Dates: Formulas and Examples

Formulas & functions · Updated November 20, 2026

To count cells that match a date in Excel, use COUNTIF with a real date value—for example, with dates in A2:A9, =COUNTIF(A2:A9,DATE(2026,3,3)) counts entries on March 3, 2026. For a period such as an entire month, use a start date and an exclusive end date; the examples below show how to handle date ranges, years, and times.

A quick COUNTIF example with dates

Suppose A2:A9 contains these Excel dates: March 1, March 3, March 3, March 10, April 3, March 31, and two entries on March 15 that include times. The year for each entry is 2026. With that layout, =COUNTIF(A2:A9,DATE(2026,3,3)) returns 2 because March 3 appears twice. The DATE function builds a date from year, month, and day numbers, avoiding uncertainty about how a typed date such as 3/3/2026 is interpreted.

COUNTIF is useful when you need one condition, such as “equals this date,” “is after this date,” or “falls on or before this date.” It does not count blank cells as dates, and the cells being checked should contain Excel date values rather than text that merely looks like a date. When the condition involves more than one rule, such as a date period plus a payment status, COUNTIFS is usually the better fit.

How Excel dates work

Excel stores dates as numbers, with the displayed date format controlling how those numbers look in a cell. That is why a cell can display March 3, 2026, while the underlying value is numeric. COUNTIF compares that underlying value; it does not simply compare the visible characters on the screen. Applying a different date display format generally does not change the date being counted.

This distinction matters when importing data. A value that looks like 3/3/2026 may be text, especially if it came from a CSV file or another application. Text dates may not compare reliably with a genuine date value, and their interpretation can depend on regional settings. To inspect a suspicious cell, change its number format temporarily to General. A genuine Excel date will usually display as a serial number; text will remain text. You can also try sorting the column or testing whether the value aligns and behaves like other numeric dates, but formatting alone is not proof that text has been converted.

Count an exact date

For an exact match, use COUNTIF with DATE as the criterion. With March dates in A2:A9, =COUNTIF(A2:A9,DATE(2026,3,3)) returns 2 for the sample list above. The year is part of the comparison: March 3, 2025, is not the same date as March 3, 2026.

It is possible to use a date typed directly into a formula, but a formula such as =COUNTIF(A2:A9,"3/3/2026") can be harder to maintain because Excel may interpret date text according to your regional date settings. DATE(2026,3,3) clearly identifies the year, month, and day and is not dependent on whether a system expects month/day/year or day/month/year. Use four-digit years to make the intended date unmistakable.

To count dates after a particular day, use a comparison criterion. With dates in A2:A9, =COUNTIF(A2:A9,">"&DATE(2026,3,3)) counts dates later than March 3, 2026; it excludes March 3 itself. Change the greater-than sign to greater-than-or-equal when the boundary day should be included. The comparison symbol is text joined to the date value with the ampersand.

Count dates within a range

COUNTIF accepts only one condition, so it cannot by itself express both a beginning and an ending boundary in the same function. COUNTIFS applies multiple conditions to corresponding ranges. A reliable pattern for a date interval is to include the start and exclude the day after the interval ends. For March 2026, with dates in A2:A9, =COUNTIFS(A2:A9,">="&DATE(2026,3,1),A2:A9,"<"&DATE(2026,4,1)) returns 7 for the sample list: all seven March entries are included, including both March 15 records.

Using an exclusive upper boundary—less than April 1 rather than less than or equal to March 31—is especially helpful if the cells contain times. A March 31 entry at 3 p.m. is later than midnight at the start of March 31, so a comparison to a date-only March 31 value may not include it when the condition is less than or equal to that value. The next-day boundary includes every time on March 31 while excluding April 1.

The same pattern works for any period. Set the lower bound to the first date you want included and the upper bound to the first date you do not want included. For a week, quarter, or custom reporting period, calculate those two boundary dates and use them as the conditions. Make sure both criteria use the same date range and that the two boundaries describe the intended period.

Count a month or year

To count a month, use its first day as the inclusive start and the first day of the next month as the exclusive end. With dates in A2:A9, =COUNTIFS(A2:A9,">="&DATE(2026,3,1),A2:A9,"<"&DATE(2026,4,1)) returns 7 for March 2026 in the sample. This method automatically accommodates months with different numbers of days, so there is no need to work out whether a month ends on the 28th, 30th, or 31st.

Include the year in both boundaries. Counting March 2026 is different from counting every March across several years. To count all dates in 2026, with the same date list in A2:A9, =COUNTIFS(A2:A9,">="&DATE(2026,1,1),A2:A9,"<"&DATE(2027,1,1)) returns 8 because every sample entry falls in 2026. For a rolling period, such as the previous 30 days, use suitable boundaries based on the reporting date; decide whether to include today and whether the period means 30 calendar dates or a particular time span.

Dates that include times

An Excel date-time value includes a date and a time. The date is the whole-number portion of the stored value, while the time is represented by a fraction of a day. As a result, an exact-date COUNTIF criterion may not match a cell that contains the same calendar date plus a time. If A2:A9 includes March 15, 2026, at 9 a.m. and 4 p.m., then =COUNTIF(A2:A9,DATE(2026,3,15)) may return 0 because the criterion represents midnight, not either time of day.

To count every entry on March 15, use a lower and upper boundary. With date-times in A2:A9, =COUNTIFS(A2:A9,">="&DATE(2026,3,15),A2:A9,"<"&DATE(2026,3,16)) returns 2 for those two records. This is the same inclusive-start, exclusive-end method used for months, applied to a single day. It counts any time from midnight on March 15 up to, but not including, midnight on March 16.

Combine dates with another condition

Use COUNTIFS when the question has additional criteria. For example, suppose A2:A9 contains the sample dates and B2:B9 contains their statuses: Paid, Paid, Open, Paid, Paid, Open, Paid, and Paid in the same row order. To count paid entries during March 2026, =COUNTIFS(A2:A9,">="&DATE(2026,3,1),A2:A9,"<"&DATE(2026,4,1),B2:B9,"Paid") returns 5. The formula requires the date to fall in March and the matching status cell to equal Paid.

In COUNTIFS, each criterion range must have the same dimensions as the others. Here, A2:A9 and B2:B9 both cover eight rows, so Excel can check each date and status on the same record. If one range starts on row 2 and another on row 3, the results may be wrong or Excel may return an error. For more than one criterion, write each range and criterion as a pair.

Common problems and ways to fix them

  • The result is zero unexpectedly: Check whether the cells contain real date values or text. Imported text dates may need to be converted before date comparisons work as intended. Also verify the year and the order of month and day.
  • Some entries on the right day are missed: Check for times in the cells. Use a one-day interval with a lower bound of the date and an upper bound of the next day instead of an exact match.
  • The formula uses a date typed in a cell: If the criterion points to a cell, verify that the cell holds a real date. For comparison criteria, join the operator to the date cell, such as ">="&D1. If D1 is intended to be a date, it should contain an Excel date value.
  • The formula shows an error or is rejected: Check that the ranges are valid and that quotation marks and parentheses are paired correctly. Excel installations configured for a different list separator may require semicolons instead of commas; the formulas here use commas as requested and as used by many Excel configurations.
  • The displayed date seems different: The cell’s number format changes how a date appears, not necessarily the stored value. Select the date cells and use Home > Number Format to choose a suitable date display. A display change does not convert text into a date.

When importing or repairing text dates, make a copy of the data first. Depending on how the text is structured, Excel’s Data > Text to Columns workflow can help convert values by specifying the correct date order. Inspect a few converted results and confirm that day and month have not been reversed before using the column in a report.

Choosing between COUNTIF and COUNTIFS

Use COUNTIF for one condition: an exact date, a date before a cutoff, or a date after a cutoff. Use COUNTIFS when you need two boundaries for a date range, or when you need a date range plus another condition such as region, category, or status. COUNTIFS is not limited to date criteria; it can test different columns at the same time as long as the ranges correspond row by row.

For a simple date interval, two COUNTIF results can be subtracted, but that is usually less readable and requires maintaining both formulas correctly. COUNTIFS expresses both boundaries together. If you need to return the matching records rather than just count them, a counting function is not the right tool; filtering the data or using a suitable dynamic-array function may be more appropriate, depending on your Excel version and task.

Frequently Asked Questions

Can COUNTIF count dates in a range?
COUNTIF handles one criterion at a time. To count dates between two boundaries, use COUNTIFS with a condition for the start and another for the end. An inclusive start and exclusive next-day end also works well when dates include times.

Why does COUNTIF return zero for a date that is visibly in the list?
The cell may contain text instead of an Excel date, may belong to a different year, or may include a time component that prevents an exact match. Check the underlying value and use a date interval if the cells include times.

How do I count all dates in a month, including times?
Use COUNTIFS with a lower boundary at the first day of the month and an upper boundary at the first day of the following month. That includes every time on each day in the month.

Can I use a cell reference for the date criterion?
Yes. A referenced cell can supply the date criterion if it contains a genuine Excel date value. For a comparison such as on or after that date, join the comparison operator to the cell reference in the criterion.

Does changing the date format fix text dates?
No. Number formatting changes the appearance of numeric values but does not convert text into date values. Convert imported text dates first, then check that Excel interpreted the day, month, and year correctly.

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.