number of tasks within an hour

[Guest]

I need a formula that does this.
Between 8:00 AM and 9:00 AM John Smith completed XX tasks. Between 9:00 AM
and 10:00 AM he did XX tasks. The information i have looks like this:
Col A Col B
John Smith 2/03/2006 9:12 AM
John Smith 2/03/2006 9:47 AM
Jane Doe 2/03/2006 9:22 AM
John Smith 2/03/2006 10:01 AM

Final result should be like this:

Employee 8 - 9am 9-10am 10-11am

John Smith 2 4 4
Jane Doe 2 3 4

Any thoughts? I can not access other web sites due to company security so
no links please.
Thanks

 

[Guest]

Did my suggestion yesterday not work? Because you should be able to add Col
C with this formula: =HOUR(B1) which will give you the hour the task was
finished.

Then, on a separate page, insert a Pivot Table (Data | Pivot Table). Your
data range will be columns A through C on Sheet1. In your layout view
(PivotTable Step 3 of 3 | Layout), drag the Employee column into the "Row"
area, drag the "Hour" column into the "Column" area, and drag the "Hour"
column into the "Data" area (where it will default to "Count", which is what
you want). Select "Ok", then select "Finish" and you'll have a chart that
does exactly what you want.

If you want fancy headings (like "8-9am"), format Col D on Sheet1 with:
=C2&"-"&(C2+1) and use that "Time" column in your Pivot Table instead of the
Hour column.

Let me know if this isn't enough information.