FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Use COUNTIF with Dates in Google Sheets

Google Sheets stores dates as serial numbers (days since December 30 , 1899). The integer part represents the day, while the decimal fraction records the time of day. Because dates are numbers, COUNTIF can treat them exactly like any other numeric value.

Example Entered as Underlying serial value Displayed as
1/1/2024 1/1/2024 45292 1/1/2024
6/15/2024 06:30 6/15/2024 06:30 45458.270833 6/15/2024 06:30
=TODAY() today’s date today’s serial today’s date

Why the serial system matters – An exact‑match COUNTIF(range, "1/1/2024") is interpreted as COUNTIF(range, 45292). If a cell contains a true date value the count works; if the cell holds a text string (e.g., '1/1/2024), the comparison fails.

1. Basic COUNTIF Syntax for Dates

COUNTIF(range, criterion)

Simple exact‑match

A
1/5/2023
1/5/2023
2/10/2023
1/5/2023
3/15/2023 
=COUNTIF(A2:A6, "1/5/2023")

Result: 3

Using comparison operators

A
12/31/2022
1/2/2023
1/15/2023
2/1/2023
3/10/2023 
=COUNTIF(A2:A6, ">=1/1/2023")

Result: 4

Best practice – use DATE (or DATEVALUE)

Hard‑coding a date string can break under different locale settings. Building the date with DATE guarantees the correct serial number.

=COUNTIF(A2:A6, ">=" & DATE(2023,1,1))

or 

=COUNTIF(A2:A6, "<" & DATEVALUE("2023-02-01"))

The first formula returns 4 for the table above; the second (dates before 1 Feb 2023) returns 3.

2. Relative vs. Absolute Date References

Reference type How it works Example criterion Typical use
Relative (e.g., “today”, “last 7 days”) Re‑evaluated on each recalculation, so the result changes over time. ">=" & TODAY() - 7 Count rows from the last 7 days.
Absolute (a fixed calendar date) The criterion never changes unless you edit the formula. ">=2024-09-01" Count rows on or after 1 Sept 2024.

Relative date example

=COUNTIF(A2:A100, ">=" & TODAY() - 30)
A (Date)
2024‑09‑01
2024‑09‑15
2024‑10‑02
2024‑10‑10

Assuming today is 2024‑10‑15, the formula returns 3 (the three dates on or after 2024‑09‑15).

Absolute date example

=COUNTIF(A2:A100, ">" & DATE(2024,9,1))

or 

=COUNTIF(A2:A100, ">" & B1)   // B1 contains 2024‑09‑01

Both count dates later than 1 Sept 2024; with the sample data above the result is 3.

3. Combining COUNTIF with AND / OR Logic

COUNTIF handles only a single criterion. For multiple conditions use:

AND logic (all conditions must be true)

=COUNTIFS(A2:A100, ">" & DATE(2023,1,1),
         A2:A100, "<" & DATE(2023,3,31))

Counts dates after 1 Jan 2023 and before 31 Mar 2023.

OR logic (any condition can be true)

=COUNTIF(A2:A100, ">" & DATE(2023,1,1))   // after Jan 1 2023
+ COUNTIF(A2:A100, "<" & DATE(2022,12,31)) // before Dec 31 2022
- COUNTIFS(A2:A100, ">" & DATE(2023,1,1),
          A2:A100, "<" & DATE(2022,12,31))   // subtract overlap (if any)

If the two ranges don’t overlap, you can simply add the two COUNTIFs.

4. Common Errors and How to Avoid Them

Error Why it Happens Fix
Using a plain string instead of a date literal A bare criterion that Sheets cannot read as a date will not match real dates. Wrap the date in DATE or DATEVALUE: COUNTIF(A2:A100, DATE(2024,1,1)) or COUNTIF(A2:A100, DATEVALUE("2024-01-01")).
Mismatched date formats Some cells contain text dates (e.g., 01/02/2024) while others hold true dates. Convert the column: =ARRAYFORMULA(IFERROR(DATEVALUE(A2:A), A2:A)).
Forgetting to build the criterion with & A hard-coded text like ">2024-01-01" depends on your locale and can fail. Concatenate: COUNTIF(A2:A, ">" & DATE(2024,1,1)).
Counting dates that also contain times An exact match fails because 2024‑01‑01 08:30 ≠ 2024‑01‑01. Strip the time: =COUNTIFS(A2:A, ">=" & DATE(2024,1,1), A2:A, "<" & DATE(2024,1,2)).

5. FAQ

How does Google Sheets store dates for COUNTIF?

Google Sheets stores each date as a serial number—the count of days since December 30 , 1899, with the fractional part representing the time of day. COUNTIF works with these serial numbers directly, so date criteria can be written as numeric expressions (>="&DATE(2023,1,1)) or as quoted strings that Sheets converts to the same serial value.

Can I use COUNTIF to count dates after a specific day?

Yes. Use a comparison operator inside the criterion string, e.g., =COUNTIF(A2:A100, ">" & DATE(2024,1,1)). The DATE function returns the correct serial number, and the & concatenates it with the > operator.

What’s the difference between COUNTIF and COUNTIFS for dates?

COUNTIF accepts a single range/criterion pair, so it can test only one condition (e.g., “after Jan 1”). COUNTIFS allows multiple range/criterion pairs, enabling AND logic such as “after Jan 1 and before Mar 31”. For OR logic you must combine separate COUNTIF results (or subtract overlaps with COUNTIFS).

Quick tip: If a column mixes true dates and text strings, wrap the range in ARRAYFORMULA(N(--A2:A)) to force conversion to numbers before counting, or use ISDATE to flag non‑date entries. This ensures COUNTIF evaluates the intended values.

Related guides