I have two types of solutions to count events in particular timeslots in Google Sheets.

We can either use formulas or the Pivot table hour grouping for the said purpose. I’ll start with the formulas.

Before that, let’s understand the scenario.

Assume I have timestamps (DateTime) of calls received from customers in a column in Google Sheets.

I want to count the number of calls received in a particular time slot, say between 10:00:00 and 12:00:00.

In this scenario, we can use the COUNTIFS formula or a COUNT and FILTER combination.

Must Read: How to Use All Google Sheets Count Functions.

Even if you know the usage of COUNTIFS in Google Sheets, you may be facing problems since you have a date-time (timestamp) column instead of a time column.

If you want to count events in hourly timeslots like the number of events at 10 AM, 11 AM, and 12 PM, then better to depend on a pivot table.

In this Google Docs Sheets tutorial, I’ve included that method also.

Count Events in a Particular Timeslot in Google Sheets

In our example below, the cell range A2:A contains the timestamps. It’s the recorded time of calls received from customers.

To count the events (here number of calls received) in the time slot 10:00:00 to 12:00:00, we can use the following COUNTIFS.

=ArrayFormula(countifs(mod(A2:A,1),">="&time(10,0,0),mod(A2:A,1),"<"&time(12,0,0)))

Here the COUNTIFS criteria are ">="&time(10,0,0) and "<"&time(12,0,0). That means the criteria are hardcoded into the formula.

To refer to the criteria given in two cells, please do as follows.

Enter the time 10:00:00 in cell C1 and 12:00:00 in cell D1. Then use the below COUNTIFS formula to count the events in the given particular timeslot.

=ArrayFormula(countifs(mod(A2:A,1),">="&C1,mod(A2:A,1),"<"&D1))

Here is the formula explanation to the COUNTIFS formula used above to count events in a particular timeslot in Google Sheets.

Formula Explanation

The core part in the above COUNTIFS time slot formula in Google Sheets is the MOD part.

The MOD formula converts the timestamp to time within the COUNTIFS formula, or in other words, we can say, it extracts the time from the timestamp AKA DateTime.

MOD is not the only option for the said purpose. Find some alternatives here – How to Extract Decimal Part of a Number in Google Sheets.

Note:- Google Sheets records the time element in a timestamp as a decimal number.

I am giving you another solution to the above count events in a particular timeslot problem. See that below.

FILTER and COUNT a Timestamp Column

In this method, we will filter the timestamps that fall in the given time slot. Then we will count them (but all in one formula).

See the formula.

=count(filter(A2:A,mod(A2:A,1)>=time(10,0,0),mod(A2:A,1)<time(12,0,0)))

The FILTER part filters the range A2:A (values in the timestamp column) that matching the timeslot (criteria). Here also, the MOD part plays an important role.

The same above COUNT + FILTER formula, using criteria in two cells.

=count(filter(A2:A,mod(A2:A,1)>=C1,mod(A2:A,1)<D1))

To count events in different (hourly) timeslots in Google Sheets we can use the Pivot Table. Let’s see how?

Counting Events in Timeslots Using Pivot Table in Google Sheets

Here also, I am going to use the sample data in A2:A12.

Here are the Pivot Table steps to count events in timeslots in Google Sheets.

1. Select A1:A12.

2. Click DATA > PIVOT TABLE (you can find all the data related commands under the DATA menu).

3. Select “Existing sheet”, then select the cell D1, and click “Create”.

4. On the Pivot table editor panel, click the “Add” button against “Rows” and select “Time”.

5. Click the “Add” button against “Values” and select “Time”. Then in the field that appears under “Summarize by”, select “Count”.

To make you understand steps 4 and 5, I’ve included the relevant image below.

6. Right-click on any cell in the pivot table range D2:D12 (for example, on cell D2).

7. Click on the shortcut menu item “Create pivot date group” and then “Hour”.

You will get the below hour-wise summary of timestamps.

That’s all about how to count events in particular timeslots in Google Sheets.

Thanks for the stay, enjoy!

Related: Create Custom Time Slot Sequences in Google Sheets.