Need help converting 15 minutes intervals into 30 minutes interval

[dennis_george]

Hi everyone,

I am trying to convert a 15-minute interval export into a 30 minutes interval and sum up the different values (Total 1 - Total 9) within that 30-minute interval.

The data table is as below:

Text

Date

TIME_Interval

Split_Number

Split_Name

Total 1

Total 2

Total 3

Total 4

Total 5

Total 6

Total 7

Total 8

Total 9

abc

1/28/2021​

12:15​

Common_1

Common_1

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

12:30​

Common_1

Common_1

2​

1​

0​

0​

0​

:02:49

:00:00

:00:00

:02:49

abc

1/28/2021​

12:45​

Common_1

Common_1

1​

1​

0​

0​

1​

:09:47

:00:00

:00:00

:09:47

abc

1/28/2021​

13:00​

Common_1

Common_1

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

15:30​

Common_1

Common_1

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

12:00​

Common_2

Common_2

1​

1​

0​

0​

1​

:04:08

:00:00

:00:00

:04:08

abc

1/28/2021​

13:15​

Common_2

Common_2

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

16:00​

Common_2

Common_2

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

16:15​

Common_2

Common_2

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

abc

1/28/2021​

16:30​

Common_2

Common_2

0​

0​

0​

0​

0​

:00:00

:00:00

:00:00

:00:00

Thank you in advance for your help. Hope that everything is clear.

 

[maabadi]

this is formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Text	Date	TIME_Interval	Split_Number	Split_Name	Total 1	Total 2	Total 3	Total 4	Total 5	Total 6	Total 7	Total 8	Total 9
2	abc	1/28/2021	12:15:00 PM	Common_1	Common_1	0	0	0	0	0	00:00	00:00	00:00	00:00
3	abc	1/28/2021	12:30:00 PM	Common_1	Common_1	2	1	0	0	0	02:49	00:00	00:00	02:49
4	abc	1/28/2021	12:45:00 PM	Common_1	Common_1	1	1	0	0	1	09:47	00:00	00:00	09:47
5	abc	1/28/2021	1:00:00 PM	Common_1	Common_1	0	0	0	0	0	00:00	00:00	00:00	00:00
6	abc	1/28/2021	3:30:00 PM	Common_1	Common_1	0	0	0	0	0	00:00	00:00	00:00	00:00
7	abc	1/28/2021	12:00:00 PM	Common_2	Common_2	1	1	0	0	1	04:08	00:00	00:00	04:08
8	abc	1/28/2021	1:15:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
9	abc	1/28/2021	4:00:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
10	abc	1/28/2021	4:15:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
11	abc	1/28/2021	4:30:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
12
13	abc	1/28/2021	12:15:00 PM	Common_1	Common_1	2	1	0	0	0	02:49	00:00	00:00	02:49
14
15	abc	1/28/2021	12:45:00 PM	Common_1	Common_1	1	1	0	0	1	09:47	00:00	00:00	09:47
16
17	abc	1/28/2021	3:30:00 PM	Common_1	Common_1	0	0	0	0	0	00:00	00:00	00:00	00:00
18	abc	1/28/2021	12:00:00 PM	Common_2	Common_2	1	1	0	0	1	04:08	00:00	00:00	04:08
19	abc	1/28/2021	1:15:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
20	abc	1/28/2021	4:00:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
21
22	abc	1/28/2021	4:30:00 PM	Common_2	Common_2	0	0	0	0	0	00:00	00:00	00:00	00:00
23
24
Sheet1

Cell Formulas
Range	Formula
D13:E22,A13:B22	A13	=IF($C13="","",A2)
C13	C13	=C2
F13:N22	F13	=IF($C13="","",IF($C14="",F2+F3,F2))
C14:C22	C14	=IF(C13="",C3,IF(ROUND(C3-C2,3)=ROUND(1/96,3),"",C3))