Formula - Calculate Calls Per Hour

[mtranx0]

I was able to setup a Pivot table to calculate how many calls per hour. Like
July 1
7am - 6 calls
8am - 1 call
9am - 8 calls
etc.

But how would I do this with a formula?

Here's the table:

DateTime	Offered
7/1/2021 7:00:00	3
7/1/2021 7:15:00	1
7/1/2021 7:30:00	1
7/1/2021 7:45:00	1
7/1/2021 8:30:00	1
7/1/2021 9:00:00	3
7/1/2021 9:15:00	2
7/1/2021 9:30:00	1
7/1/2021 9:45:00	2
7/1/2021 10:15:00	2
7/1/2021 10:30:00	0
7/1/2021 10:45:00	1
7/1/2021 11:00:00	0
7/1/2021 11:45:00	1
7/1/2021 12:00:00	1
7/1/2021 12:30:00	1
7/1/2021 12:45:00	1
7/1/2021 13:00:00	1
7/1/2021 13:15:00	0
7/1/2021 13:30:00	2
7/1/2021 13:45:00	1
7/1/2021 14:45:00	2
7/1/2021 15:00:00	2
7/1/2021 15:15:00	1
7/1/2021 15:30:00	1
7/1/2021 15:45:00	0
7/1/2021 16:15:00	1
7/1/2021 16:30:00	1
7/1/2021 16:45:00	1
7/1/2021 17:45:00	1
7/1/2021 18:00:00	1
7/2/2021 8:30:00	1
7/2/2021 8:45:00	3
7/2/2021 9:00:00	2
7/2/2021 9:15:00	2
7/2/2021 9:30:00	2
7/2/2021 9:45:00	1
7/2/2021 10:00:00	0
7/2/2021 10:15:00	1
7/2/2021 10:30:00	0
7/2/2021 10:45:00	3
7/2/2021 11:00:00	2
7/2/2021 11:15:00	4
7/2/2021 11:30:00	0
7/2/2021 12:15:00	1
7/2/2021 12:30:00	1
7/2/2021 12:45:00	1
7/2/2021 13:00:00	2
7/2/2021 13:15:00	0
7/2/2021 13:30:00	1
7/2/2021 13:45:00	2
7/2/2021 14:00:00	1
7/2/2021 14:15:00	0
7/2/2021 15:00:00	1
7/2/2021 15:15:00	0
7/2/2021 16:00:00	3
7/2/2021 16:15:00	1
7/2/2021 16:45:00	0
7/6/2021 7:00:00	1
7/6/2021 7:15:00	0
7/6/2021 7:30:00	1
7/6/2021 8:00:00	1

 

[jorismoerings]

Hi and welcome to MrExcel,

From the look to the table above, it looks like you're counting "something" in a time frame of 15 minutes. If your basic data is structured like this or does the formula needs to provide for timeframes of 15 minutes is something i can't determine.
So, generally speaking: this can be done by formula but to help you in detail: Can you provide some sample data.

 

[mtranx0]

Hi! The table is the sample data The offered column is the number of calls we get every 15 minutes. But I want to calculate how many calls per hour like
July 1
7am - 6 calls
8am - 1 call
9am - 8 calls
etc.

 

[jorismoerings]

Hi,

try this and see if this is fit for purpose:

Book1
	A	B	C	D	E	F
1	DateTime	Offered			Hours	Offered
2	1-7-2021 07:00	3			7	8
3	1-7-2021 07:15	1			8	6
4	1-7-2021 07:30	1			9	15
5	1-7-2021 07:45	1			10	7
6	1-7-2021 08:30	1			11	7
7	1-7-2021 09:00	3			12	6
8	1-7-2021 09:15	2			13	9
9	1-7-2021 09:30	1			14	3
10	1-7-2021 09:45	2			15	5
11	1-7-2021 10:15	2			16	7
12	1-7-2021 10:30	0			17	1
13	1-7-2021 10:45	1			18	1
14	1-7-2021 11:00	0
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=UNIQUE(HOUR(A2:A63-INT(A2:A63)))
F2:F13	F2	=SUMPRODUCT((HOUR(A2:A63-INT(A2:A63))=E2)*(B2:B63))
Dynamic array formulas.

 

[mtranx0]

Thank you so much!!!! Yes this helps with the summary but is there a way to calculate how many calls each hour by day? Like July 1 7am - 6 calls, July 2 7am - 0 calls etc.

 

[jorismoerings]

Something like this:

Book1
	A	B	C	D	E	F
1	DateTime	Offered			Hours	Offered
2	1-7-2021 07:00	3			1-7-2021 07:00	6
3	1-7-2021 07:15	1			1-7-2021 08:00	1
4	1-7-2021 07:30	1			1-7-2021 09:00	8
5	1-7-2021 07:45	1			1-7-2021 10:00	3
6	1-7-2021 08:30	1			1-7-2021 11:00	1
7	1-7-2021 09:00	3			1-7-2021 12:00	3
8	1-7-2021 09:15	2			1-7-2021 13:00	4
9	1-7-2021 09:30	1			1-7-2021 14:00	2
10	1-7-2021 09:45	2			1-7-2021 15:00	4
11	1-7-2021 10:15	2			1-7-2021 16:00	3
12	1-7-2021 10:30	0			1-7-2021 17:00	1
13	1-7-2021 10:45	1			1-7-2021 18:00	1
14	1-7-2021 11:00	0			2-7-2021 08:00	4
15	1-7-2021 11:45	1			2-7-2021 09:00	7
16	1-7-2021 12:00	1			2-7-2021 10:00	4
17	1-7-2021 12:30	1			2-7-2021 11:00	6
18	1-7-2021 12:45	1			2-7-2021 12:00	3
19	1-7-2021 13:00	1			2-7-2021 13:00	5
20	1-7-2021 13:15	0			2-7-2021 14:00	1
21	1-7-2021 13:30	2			2-7-2021 15:00	1
22	1-7-2021 13:45	1			2-7-2021 16:00	4
23	1-7-2021 14:45	2			6-7-2021 07:00	2
24	1-7-2021 15:00	2			6-7-2021 08:00	1
Sheet2

Cell Formulas
Range	Formula
E2:E24	E2	=UNIQUE(INT(A2:A63)+(HOUR(A2:A63)/24))
F2:F24	F2	=SUMPRODUCT((INT(A2:A63)+(HOUR(A2:A63)/24)=E2)*(B2:B63))
Dynamic array formulas.

 

[mtranx0]

Thank you so much!

Sorry I'm so new but I was wondering how you did the E column? Did you just copy =UNIQUE(INT(A2:A63)+(HOUR(A2:A63)/24)) all the way down? I tried doing that but it basically had each hour show up multiple times like:

7/1/2021 7:00:00

7/1/2021 7:00:00

7/1/2021 7:00:00

7/1/2021 7:00:00

7/1/2021 8:00:00

7/1/2021 9:00:00

7/1/2021 9:00:00

7/1/2021 9:00:00

7/1/2021 9:00:00

 

[jorismoerings]

Hi,

your account details mentioned the usage of 365 as office version.
So I’ve used the O365 function unique.
Just enter it in E2 (only!) as mentioned and O365 will automatically extend the range for you.

 

[mtranx0]

Yes thank you so much! I was working off Google Sheet that's why. This was exactly what I was looking for thanks so much!