How to Count by Hour in Excel for Calls, Orders or Visits

To count by hour in Excel, you pull the hour out of each time with HOUR and then count how often each hour appears with COUNTIF or COUNTIFS. That turns a long log of calls, orders or website visits into a simple table of busy and quiet hours, which is what you need for staffing and scheduling. This guide shows the helper-column method, a no-helper COUNTIFS formula, date-time stamps, a pivot table option and the fixes for counts that come out wrong.

How Excel stores times#

Excel stores a time as a fraction of one day. Midnight is 0, 6:00 is 0.25, 9:00 is 0.375 and 12:00 is 0.5. A date and time together, such as 3 Nov 2026 09:15, is a whole number for the date plus that fraction.

This matters for counting. You can't write =COUNTIF(B2:B9,9), because no time equals the number 9. You need either the hour as its own number, or time limits written with the TIME function. The date basics lesson explains how dates and times are stored.

All the examples below use this call log. The times in column B are real Excel times:

Method 1: HOUR helper column and COUNTIF#

The most reliable way to count by hour is a helper column that holds the hour as a plain number. In C2, type:

=HOUR(B2)

Fill it down. HOUR returns a whole number from 0 to 23, so 09:59 becomes 9 and 13:07 becomes 13. It drops the minutes; it does not round them.

Now build a summary table. Type the hours 8 to 13 in F2:F7, and in G2:

=COUNTIF($C$2:$C$9,F2)

Fill it down. The results are:

The $ signs lock the hour column so it stays put as you fill. For readable labels such as "9:00 - 10:00", use =TEXT(F2/24,"h:mm")&" - "&TEXT((F2+1)/24,"h:mm"), because dividing an hour by 24 turns it back into a time.

Method 2: COUNTIFS with time limits#

If you don't want a helper column, count the times between two limits with COUNTIFS. This counts calls from 9:00 up to, but not including, 10:00:

=COUNTIFS(B2:B9,">="&TIME(9,0,0),B2:B9,"<"&TIME(10,0,0))

The result is 3 (09:05, 09:41 and 09:59). The same column appears twice, once for each limit, and a record is counted only if it meets both. TIME(9,0,0) builds the time 9:00, and & joins it to the operator. Our article on counting between two dates with COUNTIFS uses the same "from and to" pattern.

Use >= for the start and < for the end. If you used <= with 10:00, a call at exactly 10:00:00 would be counted in two hours.

To count several hours at once, compare the helper column with a range: =COUNTIFS(C2:C9,">=9",C2:C9,"<=11") returns 6, the calls from 9:00 to 11:59.

Add more conditions with COUNTIFS#

COUNTIFS can add any other condition to the hour. To count only phone calls in the 9 o'clock hour:

=COUNTIFS(C2:C9,9,D2:D9,"Phone")

The result is 2 (C-03 and C-08). A grid of hours down the side and channels across the top, with a formula like =COUNTIFS($C$2:$C$9,$F2,$D$2:$D$9,G$1), shows the busiest hour for each channel. The COUNTIFS function guide and the article on COUNTIFS and SUMIFS with multiple conditions cover more criteria.

The same grid works for day of the week and hour. Add a second helper with =TEXT(B2,"ddd") for the day name, then count with both conditions. A colour scale from Home > Conditional Formatting turns it into a heat map of busy times.

Count by hour with date-time stamps#

Many systems export a date and time in one cell. HOUR still works, because it ignores the date part: =HOUR(B2) returns 14 for 3 Nov 2026 14:25.

The COUNTIFS time-limit method does not ignore the date. A value like 3 Nov 2026 09:15 is a large number, far above TIME(10,0,0), so it never falls between the limits. You have two options:

• Use the HOUR helper column, as in Method 1. This is the simplest choice.
• Count one specific day and hour by adding the date to each limit. With the date in H1: =COUNTIFS(B2:B9,">="&H1+TIME(9,0,0),B2:B9,"<"&H1+TIME(10,0,0)).

To count by day rather than by hour, the same ideas apply; see how to count by month for date-based grouping.

Group by hour in a pivot table#

A pivot table counts by hour without any formulas:

1. Select the data and choose Insert > PivotTable.
2. Drag the Time field to Rows and the Call field to Values, so it shows a count.
3. Right-click any time in the pivot and choose Group.
4. Select Hours only, deselect the other units, and click OK.

If the times include dates and you want one row per hour across all days, select only Hours. To split the hours by date as well, also select Days, Months and Years. If Group is greyed out or shows an error, the column contains text or blank cells.

Why your count by hour is wrong#

Most wrong counts come from the data, not the formula:

1. Times stored as text. Imported times often arrive as text, which sits on the left of the cell. HOUR can still read a text time like "09:15", but COUNTIFS limits can't. Convert the column with =VALUE(B2) or =TIMEVALUE(B2) and format it as a time.
2. Rounded times. If times were rounded, a call at 9:58 may now show as 10:00 and move hour. See how to round time in Excel before you round.
3. Durations instead of clock times. A column of call lengths is not a start time. For lengths, see calculating the difference between two times.
4. Midnight shifts. Hour 0 is midnight to 12:59 AM. Make sure your summary list starts at 0, not 1.

Practise it#

Try the exercise on this page to count calls between 9:00 and 11:59 with COUNTIFS. Then practise COUNTIFS and SUMIFS in a live spreadsheet, and practise how Excel stores dates in the date basics lesson. Each lesson checks your formula as soon as you type it.