Breaking Time Into Intervals And Performing A Count

Office 2003 with Windows XP

This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want

I have an Excel sheet with data

A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.

A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM

[Sample]

a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone

This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source

my desired output would be:


time Phone email
12:00AM
to 2 3
12:30AM

My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.

I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)

Basically, pull the hour from a2 and evaluate the mins/secs.

If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.

Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.