# How to count times between two times in Excel

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

**Excel:** `=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Call | Time |  |  | From | 0.375 |
| C-01 | 0.364583 |  |  | Before | 0.5 |
| C-02 | 0.375 |  |  | Calls |  |
| C-03 | 0.388889 |  |  |  |  |
| C-04 | 0.420139 |  |  |  |  |
| C-05 | 0.479167 |  |  |  |  |
| C-06 | 0.499306 |  |  |  |  |
| C-07 | 0.5 |  |  |  |  |
| C-08 | 0.527778 |  |  |  |  |
| C-09 | 0.552083 |  |  |  |  |
| C-10 | 0.659722 |  |  |  |  |
| C-11 | 0.6875 |  |  |  |  |
| C-12 | 0.711806 |  |  |  |  |

Formula in F3: `=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)`

Result: 5

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

## Other ways

- `=SUMPRODUCT((B2:B13>=F1)*(B2:B13<F2))`: The same count with the comparisons written out.
- `=COUNTIFS(B2:B13,">="&F1,B2:B13,"<="&F2)`: 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))`: 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.

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

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