Convert the Time into Group

bencyp

New Member
Joined
Jul 9, 2005
Messages
19
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
 

Some videos you may like

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
Joined
Dec 19, 2007
Messages
28
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
Joined
Jul 9, 2005
Messages
19
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
Joined
Feb 27, 2011
Messages
36
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
Joined
Apr 22, 2009
Messages
624
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,609
Messages
5,523,883
Members
409,541
Latest member
pgiering

This Week's Hot Topics

Top