How to count entries on a date when the cells include times in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)Count from midnight on the date up to midnight the next day: =COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1).
- Result on the example
- 6 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)Result6
B2:B1110 rows × 1 col: 3/12/2026 11:00:00, 3/13/2026 16:45:00, 3/14/2026 0:00:00, 3/14/2026 8:02:00, …A range: 10 cells in one column.
G13/14/2026The value in G1.
">="&G1">=46095"The & operator joins as text.
G1+146096The + operator adds.
"<"&G1+1"<46096"The & operator joins as text.
COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)6COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 3/12/2026 11:00:00, 3/13/2026 16:45:00, 3/14/2026 0:00:00, 3/14/2026 8:02:00, … - criteria1
">="&G1">=46095" - criteria_range2
B2:B1110 rows × 1 col: 3/12/2026 11:00:00, 3/13/2026 16:45:00, 3/14/2026 0:00:00, 3/14/2026 8:02:00, … - criteria2
"<"&G1+1"<46096"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(INT(B2:B11)=G1)) | 6 | INT drops the time, leaving the date, which can then be compared directly. |
| =COUNTIF(B2:B11,G1) | 1Different result | The formula that seems to fail with timestamps. Returns 1: only the order placed at exactly midnight. |
Common mistakes
"<="&G1as 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.
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 entries on a date when the cells include times
G2, count the orders placed at any time on the date in G1.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).
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.