Cellwise Start free

Excel guide · 4 min read

How to count by hour in Excel

Quick answerWith date-times in A2:A13 and the hour numbers 8, 9, 10... in D2:D6, use =SUMPRODUCT(--(HOUR($A$2:$A$13)=D2)) and fill it down. HOUR pulls the hour (0 to 23) out of each timestamp, so the date part doesn't get in the way. COUNTIFS with TIME(9,0,0) returns 0 on date-times, because it compares the full date and time.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHI
1ReceivedChannelHourCallsHourNov 2, 2026Nov 3, 2026
2Nov 2, 2026 8:47Phone88
3Nov 2, 2026 9:05Chat99
4Nov 2, 2026 9:00Phone1010
5Nov 2, 2026 9:59Email1111
6Nov 2, 2026 10:12Phone1212
7Nov 2, 2026 11:30Chat
8Nov 3, 2026 9:15Phone
9Nov 3, 2026 9:40Phone
10Nov 3, 2026 10:00Chat
11Nov 3, 2026 10:25Email
12Nov 3, 2026 10:58Phone
13Nov 3, 2026 12:05Phone

Download this sheet as an Excel practice file (.xlsx)

HOUR and SUMPRODUCT: one count per hour

The sheet is a support log: the date and time each contact came in, and the channel. D2:D6 hold the hours you want to report, 8 to 12. HOUR(A2:A13) turns every timestamp into its hour, the comparison with D3 gives TRUE or FALSE per row, and the double minus turns those into 1s and 0s for SUMPRODUCT to add.

Filled down, the counts are 1 for 8:00, 5 for 9:00, 4 for 10:00, 1 for 11:00 and 1 for 12:00. Each hour runs from :00 to :59, so the 9:59 email counts as 9 and the chat at exactly 10:00 counts as 10. Lock the log range with $ so it stays put when you fill down.

=SUMPRODUCT(--(HOUR($A$2:$A$13)=D3))

Result: 5

5 contacts in the 9:00 hour, across both days.

Why COUNTIFS with TIME returns 0

The obvious formula is the one below, and on this log it returns 0. A timestamp like 2 November 2026 09:05 is stored as about 46328.38: the whole number is the date and the decimal is the time. TIME(9,0,0) is only 0.375, so every timestamp is far bigger than both limits and none falls between them.

It does work when the column holds times only, with no date. For date-times, either use HOUR as above, or add a helper column with =HOUR(A2), fill it down and count it with =COUNTIF(C2:C13,9). A helper column is also the fastest option on logs with tens of thousands of rows.

For windows that don't start on the hour, like 9:30 to 10:30, compare minutes since midnight, which are whole numbers: =SUMPRODUCT((HOUR(A2:A13)*60+MINUTE(A2:A13)>=570)*(HOUR(A2:A13)*60+MINUTE(A2:A13)<630)) returns 5. 570 is 9 x 60 + 30 and 630 is 10:30.

=COUNTIFS(A2:A13,">="&TIME(9,0,0),A2:A13,"<"&TIME(10,0,0))

Result: 0

0, even though five contacts came in between 9:00 and 10:00. The dates make every value too big.

Count by hour for one day, or a day by hour grid

To count one hour on one day with COUNTIFS, add the date to the time: >= DATE(2026,11,3)+TIME(10,0,0) and < DATE(2026,11,3)+TIME(11,0,0) returns 3 for 10:00 on 3 November. This is fast on large logs and works in every version.

For a grid, put the dates across the top (H1 and I1 here) and the hours down the side (G2:G6). INT(A2:A13) is the date without the time, so multiplying the two tests counts rows that match both. Put the formula below in H4 and fill it right and down; the $ signs keep the log fixed, the date row fixed and the hour column fixed.

Add a channel by multiplying one more test: =SUMPRODUCT((HOUR(A2:A13)=10)*(B2:B13="Phone")) returns 2 phone calls in the 10:00 hour.

=SUMPRODUCT((INT($A$2:$A$13)=I$1)*(HOUR($A$2:$A$13)=$G4))

Result: 3

3 contacts on 3 November between 10:00 and 10:59.

All hours at once, and the busiest hour

In any version, FREQUENCY counts a whole list of hours in one go. Give it the hours and the hour bins: =FREQUENCY(HOUR(A2:A13),D2:D6) returns 1, 5, 4, 1, 1 and a sixth value of 0, which counts anything after the last bin (13:00 or later). In Excel 365 it spills; ignore or hide the last row.

The busiest hour is the most common hour value, so MODE answers it directly: the formula below returns 9. If two hours tie, MODE returns the one that appears first in the log, so check the counts table when it matters.

For a label like "9 AM-10 AM", use =TEXT(D3/24,"h AM/PM")&"-"&TEXT((D3+1)/24,"h AM/PM"). Dividing the hour by 24 turns it into a time that TEXT can format.

=MODE(HOUR(A2:A13))

Result: 9

9: the 9:00 hour has the most contacts (5 of 12).

No formula: a PivotTable grouped by hour

For a quick report on a big log, a PivotTable groups timestamps by hour for you.

  1. Click inside the log and choose Insert > PivotTable, then OK.
  2. Drag Received to Rows and Channel to Values (it shows Count of Channel).
  3. Right-click any date in the Rows area and choose Group.
  4. Select Hours only (deselect Months and anything else) to count across all days, or Days and Hours for a day by hour breakdown. Click OK.
  5. Drag Channel to Columns as well to split each hour by phone, chat and email.

Common mistakes

COUNTIFS with TIME on date-times

It returns 0 because the date makes every value bigger. Use HOUR, MOD(range,1), or add the date to both limits.

Counting 10:00 in the 9:00 hour

Use < for the upper limit, not <=. HOUR already does this: 10:00 has hour 10.

Timestamps stored as text

COUNTIFS won't compare text with numbers, and sorting goes wrong. Check with ISNUMBER and convert with --A2 or Data > Text to Columns first.

Forgetting the $ when filling the grid

Lock the log ($A$2:$A$13), the date row (I$1) and the hour column ($G4), or the ranges drift as you fill.

FAQ

How do I count calls per hour in Excel?

List the hours 0 to 23 (or your opening hours) in a column, then use =SUMPRODUCT(--(HOUR($A$2:$A$500)=D2)) next to each and fill down. A PivotTable grouped by Hours gives the same counts without formulas.

How do I count per half hour or 15 minutes?

Compare minutes since midnight: (HOUR(A2:A13)*60+MINUTE(A2:A13)>=570)*(HOUR(A2:A13)*60+MINUTE(A2:A13)<600) counts 9:30 to 9:59. For a slot label, use =TIME(HOUR(A2),FLOOR(MINUTE(A2),30),0) in a helper column (9:40 becomes 9:30) and count that.

How do I find the average number of calls per hour?

Divide the count for each hour by the number of days in the log. Here the 9:00 hour has 5 contacts over 2 days, an average of 2.5.

Why does my hour count include the wrong day?

HOUR ignores the date, so it counts that hour on every day. Add (INT(A2:A13)=date) inside SUMPRODUCT, or use COUNTIFS with the date added to both time limits.

Functions used

Related guides

Updated