# How to count entries on a date when the cells include times in Excel

Count from midnight on the date up to midnight the next day: `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)`.

**Excel:** `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | Placed |  |  |  | Date | 2026-03-14 |
| W-01 | 46093.458333 |  |  |  | Orders |  |
| W-02 | 46094.697917 |  |  |  |  |  |
| W-03 | 46095 |  |  |  |  |  |
| W-04 | 46095.334722 |  |  |  |  |  |
| W-05 | 46095.385417 |  |  |  |  |  |
| W-06 | 46095.520833 |  |  |  |  |  |
| W-07 | 46095.741667 |  |  |  |  |  |
| W-08 | 46095.999306 |  |  |  |  |  |
| W-09 | 46096 |  |  |  |  |  |
| W-10 | 46096.430556 |  |  |  |  |  |

Formula in G2: `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)`

Result: 6

## How it works

A timestamp is the date plus a fraction for the time, so 3/14/2026 9:15 AM is larger than 3/14/2026. `=COUNTIF(B2:B11,G1)` only matches orders at exactly midnight. Counting from G1 up to, but not including, `G1+1` catches every time of day on the 14th.

The order at midnight on the 15th belongs to the next day and is left out.

## Other ways

- `=SUMPRODUCT(--(INT(B2:B11)=G1))`: INT drops the time, leaving the date, which can then be compared directly.
- `=COUNTIF(B2:B11,G1)`: The formula that seems to fail with timestamps. (Returns 1: only the order placed at exactly midnight.)

## Common mistakes

- `"<="&G1` as the upper bound misses everything after midnight. Use `"<"&G1+1`.
- Timestamps exported as text ("2026-03-14 09:15") aren't counted. Convert them with DATEVALUE plus TIMEVALUE, or Text to Columns.

## Questions

**How do I count entries per day in a table?**

List the dates in a column and fill `=COUNTIFS($B$2:$B$11,">="&E2,$B$2:$B$11,"<"&E2+1)` down.

**How do I count entries on a date within business hours?**

Add the hours to the date: `=COUNTIFS(B2:B11,">="&G1+9/24,B2:B11,"<"&G1+17/24)`.

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-entries-on-a-date-with-timestamps