Sum values between hour range

[buer4499]

Hello,

I was wondering if there is a way I can get the total of each hour interval based on the date and time.

So example, the sum of scheduled between 00:00-01:00 but making it date dependant.

I've attached example data and how it needs to be formatted after.

Book1
	A	B	C
1	Date	Interval	Scheduled
2	05/07/2021	00:00	28
3	05/07/2021	00:15	25
4	05/07/2021	00:30	19
5	05/07/2021	00:45	18
6	05/07/2021	01:00	19
7	05/07/2021	01:15	18
8	05/07/2021	01:30	24
9	05/07/2021	01:45	31
10	05/07/2021	02:00	58
11	05/07/2021	02:15	87
12	05/07/2021	02:30	102
13	05/07/2021	02:45	99
14	05/07/2021	03:00	104
15	05/07/2021	03:15	107
16	05/07/2021	03:30	107
17	05/07/2021	03:45	108
18	05/07/2021	04:00	111
19	05/07/2021	04:15	102
20	05/07/2021	04:30	93
21	05/07/2021	04:45	75
22	05/07/2021	05:00	72
23	05/07/2021	05:15	60
24	05/07/2021	05:30	43
25	05/07/2021	05:45	37
26	05/07/2021	06:00	28
27	05/07/2021	06:15	23
28	05/07/2021	06:30	19
29	05/07/2021	06:45	18
30	05/07/2021	07:00	18
31	05/07/2021	07:15	16
32	05/07/2021	07:30	19
33	05/07/2021	07:45	31
34	05/07/2021	08:00	62
35	05/07/2021	08:15	97
36	05/07/2021	08:30	107
37	05/07/2021	08:45	107
38	05/07/2021	09:00	104
Sheet1

Book1
	A	B	C
1	Date	Interval	Scheduled
2	05/07/2021	0000
3	05/07/2021	0100
4	05/07/2021	0200
5	05/07/2021	0300
6	05/07/2021	0400
7	05/07/2021	0500
8	05/07/2021	0600
9	05/07/2021	0700
10	05/07/2021	0800
Sheet2

 

[arthurbr]

Perhaps try a Pivot Table grouping time by hours

 

[JimM]

Could try SUMIFS

Need to change the intervals to time values though eg 00:00, 01:00, 02:00 etc

=SUMIFS(Sheet1!$C$2:$C$38,Sheet1!$A$2:$A$38,A2,Sheet1!$B$2:$B$38,"<="&B2,Sheet1!$B$2:$B$38,">"&B2-TIME(0,59,0))

 

[buer4499]

Could try SUMIFS

Need to change the intervals to time values though eg 00:00, 01:00, 02:00 etc

=SUMIFS(Sheet1!$C$2:$C$38,Sheet1!$A$2:$A$38,A2,Sheet1!$B$2:$B$38,"<="&B2,Sheet1!$B$2:$B$38,">"&B2-TIME(0,59,0))

This has just returned a 0 value, I'll copy the actual worksheet below

 

[buer4499]

I'm looking to fill in the scheduled of the below:

Leavesden CC Staffing Plan.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Russian		French		German		Spanish		Italian		Swedish
2		Date	Interval	Scheduled	Interval	Scheduled	Interval	Scheduled	Interval	Scheduled	Interval	Scheduled	Interval	Scheduled
3	443820	05/07/2021	00:00	0	0000	0	0000	0	0000	0	0000	0	0000	0
4	443820.0416666666666667	05/07/2021	01:00	0	0100	0	0100	0	0100	0	0100	0	0100	0
5	443820.0833333333333333	05/07/2021	02:00	0	0200	0	0200	0	0200	0	0200	0	0200	0
6	443820.125	05/07/2021	03:00	0	0300	0	0300	0	0300	0	0300	0	0300	0
7	443820.166666666666667	05/07/2021	04:00	0	0400	0	0400	0	0400	0	0400	0	0400	0
8	443820.208333333333333	05/07/2021	05:00	0	0500	0	0500	0	0500	0	0500	0	0500	0
9	443820.25	05/07/2021	06:00	0	0600	0	0600	0	0600	0	0600	0	0600	0
10	443820.291666666666667	05/07/2021	07:00	0	0700	0	0700	0	0700	0	0700	0	0700	0
11	443820.333333333333333	05/07/2021	08:00	0	0800	7	0800	3	0800	7	0800	7	0800	1
12	443820.375	05/07/2021	09:00	0	0900	7	0900	3	0900	7	0900	7	0900	1
13	443820.416666666666667	05/07/2021	10:00	0	1000	7	1000	3	1000	7	1000	7	1000	1
14	443820.458333333333333	05/07/2021	11:00	0	1100	7	1100	3	1100	7	1100	7	1100	1
15	443820.5	05/07/2021	12:00	0	1200	7	1200	3	1200	7	1200	7	1200	1
16	443820.541666666666667	05/07/2021	13:00	0	1300	7	1300	3	1300	7	1300	7	1300	1
17	443820.583333333333333	05/07/2021	14:00	0	1400	7	1400	3	1400	7	1400	7	1400	1
18	443820.625	05/07/2021	15:00	0	1500	7	1500	3	1500	7	1500	7	1500	1
19	443820.666666666666667	05/07/2021	16:00	0	1600	7	1600	3	1600	7	1600	7	1600	1
20	443820.708333333333333	05/07/2021	17:00	0	1700	0	1700	0	1700	0	1700	0	1700	0
21	443820.75	05/07/2021	18:00	0	1800	0	1800	0	1800	0	1800	0	1800	0
22	443820.791666666666667	05/07/2021	19:00	0	1900	0	1900	0	1900	0	1900	0	1900	0
23	443820.833333333333333	05/07/2021	20:00	0	2000	0	2000	0	2000	0	2000	0	2000	0
24	443820.875	05/07/2021	21:00	0	2100	0	2100	0	2100	0	2100	0	2100	0
25	443820.916666666666667	05/07/2021	22:00	0	2200	0	2200	0	2200	0	2200	0	2200	0
26	443820.958333333333333	05/07/2021	23:00	0	2300	0	2300	0	2300	0	2300	0	2300	0
27	443831	06/07/2021	00:00	0	0000	0	0000	0	0000	0	0000	0	0000	0
Latest View ( Hourly)

Cell Formulas
Range	Formula
B3:B27	B3	=C3&D3
C3:C27	C3	='First View ( Hourly)'!B3
E3:E27	E3	=SUMIFS('Latest View (15 Mins)'!$E:$E,'Latest View ( Hourly)'!C:C,'Latest View ( Hourly)'!D3,'Latest View (15 Mins)'!$D:$D,"<="&'Latest View ( Hourly)'!D3,'Latest View (15 Mins)'!$D:$D,">"&'Latest View ( Hourly)'!D3-TIME(0,59,0))

below is my data tab

Leavesden CC Staffing Plan.xlsx
	B	C	D	E	F
2		Date	RU Interval	RU Scheduled	Hourly
3	443820	05/07/2021	00:00	28	89
4	443820.0104166666666667	05/07/2021	00:15	25
5	443820.0208333333333333	05/07/2021	00:30	19
6	443820.03125	05/07/2021	00:45	18
7	443820.0416666666666667	05/07/2021	01:00	19	92
8	443820.0520833333333333	05/07/2021	01:15	18
9	443820.0625	05/07/2021	01:30	24
10	443820.0729166666666667	05/07/2021	01:45	31
11	443820.0833333333333333	05/07/2021	02:00	58	346
12	443820.09375	05/07/2021	02:15	87
13	443820.104166666666667	05/07/2021	02:30	102
14	443820.114583333333333	05/07/2021	02:45	99
15	443820.125	05/07/2021	03:00	104	427
16	443820.135416666666667	05/07/2021	03:15	107
17	443820.145833333333333	05/07/2021	03:30	107
18	443820.15625	05/07/2021	03:45	108
19	443820.166666666666667	05/07/2021	04:00	111	381
20	443820.177083333333333	05/07/2021	04:15	102
21	443820.1875	05/07/2021	04:30	93
22	443820.197916666666667	05/07/2021	04:45	75
23	443820.208333333333333	05/07/2021	05:00	72	213
24	443820.21875	05/07/2021	05:15	60
25	443820.229166666666667	05/07/2021	05:30	43
26	443820.239583333333333	05/07/2021	05:45	37
27	443820.25	05/07/2021	06:00	28	87
28	443820.260416666666667	05/07/2021	06:15	23
29	443820.270833333333333	05/07/2021	06:30	19
30	443820.28125	05/07/2021	06:45	18
31	443820.291666666666667	05/07/2021	07:00	18	83
32	443820.302083333333333	05/07/2021	07:15	16
33	443820.3125	05/07/2021	07:30	19
34	443820.322916666666667	05/07/2021	07:45	31
35	443820.333333333333333	05/07/2021	08:00	62	372
36	443820.34375	05/07/2021	08:15	97
Latest View (15 Mins)

Cell Formulas
Range	Formula
B3:B36	B3	=C3&D3
C3	C3	=B1
C4:C36	C4	=C3
F3,F35,F31,F27,F23,F19,F15,F11,F7	F3	=SUM(E3:E6)

 

[arthurbr]

Why is a Pivot Table not an option?

 

[buer4499]

Why is a Pivot Table not an option?

The value is being returned into another table so would like the value to automatically go in there