# Why does COUNTIFS miss the last day of a date range?

The dates have times, so "="&E1,A2:A8,"<"&E2+1).

**Excel:** `=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Created | Ticket |  | From | 2026-10-01 |
| 46295.6 | T-301 |  | To | 2026-10-31 |
| 46296.4 | T-302 |  | Count |  |
| 46310.75 | T-303 |  |  |  |
| 46326.2 | T-304 |  |  |  |
| 46326.9 | T-305 |  |  |  |
| 46327.1 | T-306 |  |  |  |
| 46315.5 | T-307 |  |  |  |

Formula in E3: `=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)`

Result: 5

## How it works

The tickets carry a time as well as a date. "<="&E2 means up to midnight at the start of October 31, so the two tickets created later that day are left out. Counting up to, but not including, the next day ("<"&E2+1) takes in the whole last day: 5 tickets.

## Other ways

- `=SUMPRODUCT((INT(A2:A8)>=E1)*(INT(A2:A8)<=E2))`: Drops the times with INT, then compares whole dates.

## Common mistakes

- A date-only format hides the times, so the data looks like whole dates. Switch to a date and time format to check.
- The same fix applies to SUMIFS and AVERAGEIFS with date ranges.

## Questions

**How do I count a single day?**

`=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E1+1)`.

**How do I strip the times from the column?**

A helper column with `=INT(A2)`, formatted as a date.

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/countifs-misses-last-day-of-date-range