How to count times between two times in Excel

Q: How do I count times after a cutoff, like late arrivals?

Use one condition: =COUNTIF(B2:B13,">"&F1) with 9:00 AM in F1.

Q: How do I count a shift that crosses midnight, like 10 PM to 6 AM?

Add the two parts: =COUNTIF(B2:B13,">="&F1)+COUNTIF(B2:B13,"<"&F2).

Q: How do I count by hour of the day?

Use a window per hour, from h/24 up to (h+1)/24: =COUNTIFS(B2:B13,">="&10/24,B2:B13,"<"&11/24) counts the calls from 10 to 11 AM.

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)

Times are fractions of a day, so COUNTIFS compares them like numbers: =COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2).

Result on the example: 5 in F3
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.

How it works

A time is stored as a fraction of a day: 9:00 AM is 0.375 and noon is 0.5. So COUNTIFS can compare times with the same operators as numbers, joined to the cells that hold the start and end.

The 9:00 call counts and the 12:00 call doesn't, because the end is "before 12:00". That way back-to-back slots never count a call twice.

=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)

Result5

B2:B1312 rows × 1 col: 8:45 AM, 9:00 AM, 9:20 AM, 10:05 AM, …
A range: 12 cells in one column.
F19:00 AM
The value in F1.
">="&F1">=0.375"
The & operator joins as text.
F212:00 PM
The value in F2.
"<"&F2"<0.5"
The & operator joins as text.
COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)5
COUNTIFS: Counts rows that meet every condition.
- criteria_range1B2:B1312 rows × 1 col: 8:45 AM, 9:00 AM, 9:20 AM, 10:05 AM, …
- criteria1">="&F1">=0.375"
- criteria_range2B2:B1312 rows × 1 col: 8:45 AM, 9:00 AM, 9:20 AM, 10:05 AM, …
- criteria2"<"&F2"<0.5"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2). We ran it in Google Sheets mode on the same data and it returns 5.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT((B2:B13>=F1)*(B2:B13<F2))	5	The same count with the comparisons written out.
=COUNTIFS(B2:B13,">="&F1,B2:B13,"<="&F2)	6Different result	Includes calls exactly at the end time. Also counts the call at 12:00 PM.
=SUMPRODUCT((MOD(B2:B13,1)>=F1)*(MOD(B2:B13,1)<F2))	5	Use this when the cells hold dates with times: MOD keeps only the time of day, so the dates don't get in the way.

Common mistakes

If the cells hold a date and a time, they are much bigger than 0.5 and nothing matches. Compare only the time part: =SUMPRODUCT((MOD(B2:B13,1)>=F1)*(MOD(B2:B13,1)<F2)).
Times typed as text ("9:20 am" with an odd space) are not counted. Check that the times are right-aligned.
Typing the time inside the criteria, like ">=9:00", works in some versions but is fragile; keep times in cells.

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 times between two times

In F3, count the calls from the time in F1 up to, but not including, the time in F2.

Questions

How do I count times after a cutoff, like late arrivals?

Use one condition: =COUNTIF(B2:B13,">"&F1) with 9:00 AM in F1.

How do I count a shift that crosses midnight, like 10 PM to 6 AM?

Add the two parts: =COUNTIF(B2:B13,">="&F1)+COUNTIF(B2:B13,"<"&F2).

How do I count by hour of the day?

Use a window per hour, from h/24 up to (h+1)/24: =COUNTIFS(B2:B13,">="&10/24,B2:B13,"<"&11/24) counts the calls from 10 to 11 AM.

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.