Help with analysing a large series of time data

[Mchgh9]

Hi,

I have a series of time in the following format.

Column U

[TABLE="width: 113"]
<COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><TBODY>[TR]
[TD="class: xl63, width: 150, bgcolor: transparent"]Works Recorded Time[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]07:26[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:12[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]08:21[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 150, bgcolor: transparent"]08:28[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:29[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:31[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:40[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:42[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:43[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 150, bgcolor: transparent"]08:44[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:47[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:48[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:49[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:50[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:51[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:52[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:53[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:54[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:55[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:56[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:57[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:58[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]08:59[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:00[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:01[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:02[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:03[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:04[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:05[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:06[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:07[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:08[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:09[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:11[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:13[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:14[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:15[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:16[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:17[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:18[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:19[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:20[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:21[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]09:22[/TD]
[/TR]
</TBODY>[/TABLE]

When I am creating pivot tables and use this data it makes it incredibly hard to anaylse the data as there as so many values (There are around 10,000 entries between office hours) I'm looking to group the data into sets e.g. 09:00-09:29, 09:30-09:59, 10:00-10:29, 10:30-10:59 etc etc

Can anyone help with suggestions on how to do this please?

Thanks

Dave

 

[Asala42]

There are several formulas you could add to round these times down to the half hour
=FLOOR(A2,30/1440)

Converts: 7:26 AM to 7:00 AM.