How to count dates in the last 30 days in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)Count dates after the as-of date minus 30 and up to the as-of date: =COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1), with =TODAY() in G1.
- Result on the example
- 7 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
G1-G2 is the date 30 days before the as-of date. Counting dates strictly after it and on or before G1 covers exactly 30 days, the as-of date included. COUNTIFS needs both bounds so that future dates stay out.
March 1 is exactly 30 days back and falls just outside; the April 2 sign-up is in the future and is left out too.
=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)Result7
B2:B1312 rows × 1 col: 1/28/2026, 2/10/2026, 2/25/2026, 3/1/2026, …A range: 12 cells in one column.
G13/31/2026The value in G1.
G230The value in G2.
G1-G246082The - operator subtracts.
">"&G1-G2">46082"The & operator joins as text.
"<="&G1"<=46112"The & operator joins as text.
COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)7COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1312 rows × 1 col: 1/28/2026, 2/10/2026, 2/25/2026, 3/1/2026, … - criteria1
">"&G1-G2">46082" - criteria_range2
B2:B1312 rows × 1 col: 1/28/2026, 2/10/2026, 2/25/2026, 3/1/2026, … - criteria2
"<="&G1"<=46112"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1). We ran it in Google Sheets mode on the same data and it returns 7.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B13>G1-G2)*(B2:B13<=G1)) | 7 | The same window as an array test. |
| =COUNTIF(B2:B13,">"&G1-G2) | 8Different result | Only a lower bound: fine when the list can't contain future dates. Also counts the April 2 sign-up. |
Common mistakes
- Decide whether "last 30 days" includes today and the day 30 days ago;
">="&G1-30covers 31 days. Spell the rule out so reports agree. - Timestamps on the as-of day are later than midnight, so
"<="&G1misses them. Use"<"&G1+1for timestamps.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count dates in the last 30 days
G3, count the sign-ups in the G2 days ending on the date in G1.Questions
How do I count the last 7 days?
Change G2 to 7, or write it in: =COUNTIFS(B2:B13,">"&TODAY()-7,B2:B13,"<="&TODAY()).
How do I count this calendar month instead?
Use the month bounds: =COUNTIFS(B2:B13,">"&EOMONTH(G1,-1),B2:B13,"<="&EOMONTH(G1,0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.