Convert the Time into Group

[bencyp]

Hi,

I have the following data and I want to group all the time into 30 min group.

Time Count
1:00 48
1:13 50
1:14 53
1:15 54
1:15 54
1:16 55
1:16 59
1:17 0
1:18 2
1:19 6
1:21 8
1:22 11
1:22 13
1:23 14
1:30 16
1:30 18
1:37 19
1:38 20
1:39 21
1:40 21
1:41 21
1:42 22
1:43 23
1:44 24
1:45 25
1:46 26
1:48 27
1:50 28
1:59 29
2:00 30
2:02 31
2:06 32
2:08 33
2:11 34
2:13 37
2:14 37
2:16 38
2:24 40
2:25 41
2:26 43
2:27 44
2:28 45
2:29 48
2:30 53
2:31 55
2:32 56
2:33 58
2:34 0
2:37 2
2:37 2
2:38 3
2:40 10
2:41 12
2:43 20


And if I group in 30 min time interval I will get the following value. Can this be done using some formula?

Time Count
1:00 427
1:30 340
2:00 533
2:30 271

 

[Cronkgarrow]

Put this table somewhere (I'm using I2 to J8).

<table x:str="" style="border-collapse: collapse; width: 96pt;" border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" x:num="0" align="right" height="17" width="64">Time
00:00</td> <td class="xl25" style="width: 48pt;" x:num="" align="right" width="64">Category
0.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="2.0833333333333332E-2" align="right" height="17">00:30</td> <td class="xl25" x:num="" align="right">0.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="4.1666666666666664E-2" align="right" height="17">01:00</td> <td class="xl25" x:num="" align="right">1.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="6.25E-2" align="right" height="17">01:30</td> <td class="xl25" x:num="" align="right">1.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="8.3333333333333329E-2" align="right" height="17">02:00</td> <td class="xl25" x:num="" align="right">2.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.10416666666666667" align="right" height="17">02:30</td> <td class="xl25" x:num="" align="right">2.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.125" align="right" height="17">03:00</td> <td class="xl25" x:num="" align="right">3.0</td> </tr> </tbody></table>
Then in the 3rd column eg "Column C" use something like =VLOOKUP(A2,$I$2:$J$8,2) and fill it down through the whole column of data.

This will then give a decimal category next to your times.

You then make a sumif formula (in column K) like

=SUMIF($C$2:$C$55,J2,$B$2:$B$55) Where C is your newly parsed decimal category, and B is your count column.

<table x:str="" style="border-collapse: collapse; width: 150pt;" border="0" cellpadding="0" cellspacing="0" width="200"><col style="width: 48pt;" width="64"> <col style="width: 54pt;" width="72"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; width: 48pt;" height="17" width="64">Time</td> <td class="xl26" style="width: 54pt;" width="72">Category</td> <td class="xl26" style="width: 48pt;" width="64"> Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0" align="right" height="17">00:00</td> <td class="xl25" x:num="" align="right">0.0</td> <td x:num="" align="right">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="2.0833333333333332E-2" align="right" height="17">00:30</td> <td class="xl25" x:num="" align="right">0.5</td> <td x:num="" align="right">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="4.1666666666666664E-2" align="right" height="17">01:00</td> <td class="xl25" x:num="" align="right">1.0</td> <td x:num="" align="right">427</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="6.25E-2" align="right" height="17">01:30</td> <td class="xl25" x:num="" align="right">1.5</td> <td x:num="" align="right">340</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="8.3333333333333329E-2" align="right" height="17">02:00</td> <td class="xl25" x:num="" align="right">2.0</td> <td x:num="" align="right">533</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.10416666666666667" align="right" height="17">02:30</td> <td class="xl25" x:num="" align="right">2.5</td> <td x:num="" align="right">271</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.125" align="right" height="17">03:00</td> <td class="xl25" x:num="" align="right">3.0</td> <td x:num="" align="right">0</td> </tr> </tbody></table>

I hope that makes some sense.

 

[bencyp]

Hi Cronkgarrow,

Thank you very much. This does my job.

Please explain how a Vlookup in the time format is taking the nearest value?

Regards,
Bency

 

[baodinh]

The complete vlookup formula should have looked like =VLOOKUP(A2,$I$2:$J$8,2,TRUE). The TRUE parameter means look for approximate match and assumes the lookup table has been sorted in ascending order. Had the TRUE been FALSE, it would have looked for an exact match and returned a #N/A error if not found.

 

[delaneyjm]

While it appears you have a solution, I'm just going to throw in my two cents.

I would used a Pivot Table for this. First, I would have created a helper column with the following formula since you can't group Pivot Table items by the half hour.

Excel Workbook
	A	B	C
1	Time	Grouping	Count
2	1:00	1:00	48
Sheet1

Then create a Pivot Table using the Grouping column and Count column.