The first column is the start time and the second is the end time of a call. I want to count the number of calls that are going on in 1 hour time slots.
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 08:04[/TD]
[TD="align: right"]15/02/2018 09:22[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:05[/TD]
[TD="align: right"]15/02/2018 08:12[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:12[/TD]
[TD="align: right"]15/02/2018 09:22[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:24[/TD]
[TD="align: right"]15/02/2018 08:56[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:27[/TD]
[TD="align: right"]15/02/2018 08:56[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:28[/TD]
[TD="align: right"]15/02/2018 08:34[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:31[/TD]
[TD="align: right"]15/02/2018 08:34[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:59[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:59[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:03[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:04[/TD]
[TD="align: right"]15/02/2018 09:08[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:05[/TD]
[TD="align: right"]15/02/2018 09:07[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:12[/TD]
[TD="align: right"]15/02/2018 10:34[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 09:12[/TD]
[TD="align: right"]15/02/2018 10:31[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:15[/TD]
[TD="align: right"]15/02/2018 10:33[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:16[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:17[/TD]
[TD="align: right"]15/02/2018 10:31[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:17[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:19[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:30[/TD]
[TD="align: right"]15/02/2018 10:10[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:30[/TD]
[TD="align: right"]15/02/2018 10:10[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:55[/TD]
[TD="align: right"]15/02/2018 10:35[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:58[/TD]
[TD="align: right"]15/02/2018 11:01[/TD]
[/TR]
</tbody>[/TABLE]
I also have start and end time criteria each with an increment of 1 hour.
[TABLE="width: 401"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 07:00[/TD]
[TD="align: right"]15/02/2018 08:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:00[/TD]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]
5[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]15/02/2018 10:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 10:00[/TD]
[TD="align: right"]15/02/2018 11:00[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
For example, if we look at 08:00 to 09:00 time slot, I used the below function..
=COUNTIFS('start time range',">="&'08:00','end time range',"<"&'09:00')
.. it gives out 5 when I wanted the result to be 9. This is because calls that went past 09:00 (in red) were ignored. How can I amend the function so it counts those calls as well?
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 08:04[/TD]
[TD="align: right"]15/02/2018 09:22[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:05[/TD]
[TD="align: right"]15/02/2018 08:12[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:12[/TD]
[TD="align: right"]15/02/2018 09:22[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:24[/TD]
[TD="align: right"]15/02/2018 08:56[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:27[/TD]
[TD="align: right"]15/02/2018 08:56[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:28[/TD]
[TD="align: right"]15/02/2018 08:34[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:31[/TD]
[TD="align: right"]15/02/2018 08:34[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:59[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:59[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:03[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:04[/TD]
[TD="align: right"]15/02/2018 09:08[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:05[/TD]
[TD="align: right"]15/02/2018 09:07[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:12[/TD]
[TD="align: right"]15/02/2018 10:34[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 310"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 09:12[/TD]
[TD="align: right"]15/02/2018 10:31[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:15[/TD]
[TD="align: right"]15/02/2018 10:33[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:16[/TD]
[TD="align: right"]15/02/2018 09:30[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:17[/TD]
[TD="align: right"]15/02/2018 10:31[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:17[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:19[/TD]
[TD="align: right"]15/02/2018 10:02[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:30[/TD]
[TD="align: right"]15/02/2018 10:10[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:30[/TD]
[TD="align: right"]15/02/2018 10:10[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:55[/TD]
[TD="align: right"]15/02/2018 10:35[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:58[/TD]
[TD="align: right"]15/02/2018 11:01[/TD]
[/TR]
</tbody>[/TABLE]
I also have start and end time criteria each with an increment of 1 hour.
[TABLE="width: 401"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"]15/02/2018 07:00[/TD]
[TD="align: right"]15/02/2018 08:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 08:00[/TD]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]
5[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 09:00[/TD]
[TD="align: right"]15/02/2018 10:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018 10:00[/TD]
[TD="align: right"]15/02/2018 11:00[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
For example, if we look at 08:00 to 09:00 time slot, I used the below function..
=COUNTIFS('start time range',">="&'08:00','end time range',"<"&'09:00')
.. it gives out 5 when I wanted the result to be 9. This is because calls that went past 09:00 (in red) were ignored. How can I amend the function so it counts those calls as well?