Distributing Data per 30 mins

[chaboyski]

Hi Team,

just wanted to check if there would be a simpler way to do this.

Final data would be Cell I11 - I34

i wanted to distribute and add the data from example, B2 - b8.

long method is cell A11 -I34

legend is, if the data is at 8am, it would end at 16:30, just add 8.5 hours.

not sure if the explanation is clear.

shift.xlsx
	A	B	C	D	E	F	G	H	I
1	Schedule	1-Sep	2-Sep	3-Sep	4-Sep			Schedule
2	8:00:00 AM	54	2	53	55			8:00	16:30
3	8:30:00 AM	37	0	37	39			8:30	17:00
4	9:00:00 AM	20	0	20	18			9:00	17:30
5	9:30:00 AM	25	0	22	22			9:30	18:00
6	10:00:00 AM	2	1	2	2			10:00	18:30
7	10:30:00 AM	4	0	4	4			10:30	19:00
8	11:00:00 AM	40	3	41	39			11:00	19:30
9
10									1-Sep
11	8:00	54							54
12	8:30	54	37						91
13	9:00	54	37	20					111
14	9:30	54	37	20	25				136
15	10:00	54	37	20	25	2			138
16	10:30	54	37	20	25	2	4		142
17	11:00	54	37	20	25	2	4	40	182
18	11:30	54	37	20	25	2	4	40	182
19	12:00	54	37	20	25	2	4	40	182
20	12:30	54	37	20	25	2	4	40	182
21	13:00	54	37	20	25	2	4	40	182
22	13:30	54	37	20	25	2	4	40	182
23	14:00	54	37	20	25	2	4	40	182
24	14:30	54	37	20	25	2	4	40	182
25	15:00	54	37	20	25	2	4	40	182
26	15:30	54	37	20	25	2	4	40	182
27	16:00	54	37	20	25	2	4	40	182
28	16:30	54	37	20	25	2	4	40	182
29	17:00		37	20	25	2	4	40	128
30	17:30			20	25	2	4	40	91
31	18:00				25	2	4	40	71
32	18:30					2	4	40	46
33	19:00						4	40	44
34	19:30							40	40
Sheet3

Cell Formulas
Range	Formula
H2:H8	H2	=A2
I2:I8	I2	=H2+"8:30"
A12:A34	A12	=A11+"00:30"
I11:I34	I11	=SUM(B11:H11)

 

[JamesCanale]

This might do it. Adding the half-hour sometimes gives you trouble with rounding errors.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I
1	Schedule	9/1/2023	9/2/2023	9/3/2023	9/4/2023			Schedule
2	8:00:00 AM	54	2	53	55			8:00:00 AM	4:30:00 PM
3	8:30:00 AM	37	0	37	39			8:30:00 AM	5:00:00 PM
4	9:00:00 AM	20	0	20	18			9:00:00 AM	5:30:00 PM
5	9:30:00 AM	25	0	22	22			9:30:00 AM	6:00:00 PM
6	10:00:00 AM	2	1	2	2			10:00:00 AM	6:30:00 PM
7	10:30:00 AM	4	0	4	4			10:30:00 AM	7:00:00 PM
8	11:00:00 AM	40	3	41	39			11:00:00 AM	7:30:00 PM
9
10
11	8:00:00 AM	54	2	53	55
12	8:30:00 AM	91	2	90	94
13	9:00:00 AM	111	2	110	112
14	9:30:00 AM	136	2	132	134
15	10:00:00 AM	138	3	134	136
16	10:30:00 AM	142	3	138	140
17	11:00:00 AM	182	6	179	179
18	11:30:00 AM	182	6	179	179
19	12:00:00 PM	182	6	179	179
20	12:30:00 PM	182	6	179	179
21	1:00:00 PM	182	6	179	179
22	1:30:00 PM	182	6	179	179
23	2:00:00 PM	182	6	179	179
24	2:30:00 PM	182	6	179	179
25	3:00:00 PM	182	6	179	179
26	3:30:00 PM	182	6	179	179
27	4:00:00 PM	182	6	179	179
28	4:30:00 PM	182	6	179	179
29	5:00:00 PM	128	4	126	124
30	5:30:00 PM	91	4	89	85
31	6:00:00 PM	71	4	69	67
32	6:30:00 PM	46	4	47	45
33	7:00:00 PM	44	3	45	43
34	7:30:00 PM	40	3	41	39
Sheet19

Cell Formulas
Range	Formula
H2:H8	H2	=A2
I2:I8	I2	=H2+"8:30"
B11:E34	B11	=SUMPRODUCT(B$2:B$8,--($A$2:$A$8<=$A11),--($A$2:$A$8+8.51/24>=$A11))