# Convert the Time into Group

#### bencyp

##### New Member
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Cronkgarrow

##### New Member
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.

Last edited:

#### bencyp

##### New Member
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

##### New Member
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

##### Well-known Member
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
ABC
1TimeGroupingCount
21:001:0048
Sheet1

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

Replies
5
Views
24
Replies
5
Views
43
Replies
4
Views
79
Replies
8
Views
57
Replies
3
Views
34