# How to count dates in the last 30 days in Excel

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.

**Excel:** `=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Customer | Signed up |  |  |  | As of | 2026-03-31 |
| Northwind | 2026-01-28 |  |  |  | Days | 30 |
| Contoso | 2026-02-10 |  |  |  | Sign-ups |  |
| Fabrikam | 2026-02-25 |  |  |  |  |  |
| Litware | 2026-03-01 |  |  |  |  |  |
| Adatum | 2026-03-02 |  |  |  |  |  |
| Proseware | 2026-03-08 |  |  |  |  |  |
| Wingtip | 2026-03-15 |  |  |  |  |  |
| Tailspin | 2026-03-15 |  |  |  |  |  |
| Woodgrove | 2026-03-22 |  |  |  |  |  |
| Alpine | 2026-03-30 |  |  |  |  |  |
| Coho | 2026-03-31 |  |  |  |  |  |
| Lucerne | 2026-04-02 |  |  |  |  |  |

Formula in G3: `=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)`

Result: 7

## 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.

## Other ways

- `=SUMPRODUCT((B2:B13>G1-G2)*(B2:B13<=G1))`: The same window as an array test.
- `=COUNTIF(B2:B13,">"&G1-G2)`: 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-30` covers 31 days. Spell the rule out so reports agree.
- Timestamps on the as-of day are later than midnight, so `"<="&G1` misses them. Use `"<"&G1+1` for timestamps.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-dates-in-the-last-30-days