Count Function in Date Field

[Rajesh Sharma]

Hi all,
I have a database which gives me the total calls taken by different
executives in a day and also the time at which each particular call was
recorded eg. 7:53:35 PM
i need to find a count of the calls taken by each in every hour . ie
hourly call breakup .. eg.

Time Slab Exec A Exec B Exec C
----------------------------------------------------------
Between 8 to 9 AM 10 8 5
Between 9 to 10 AM 8 11 12

i want to do this in Excel 97'. How can I get this using the countif
function
Please revert asap

Warm regards,
Rajesh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Rajesh,

Here's one formula;a for between 7 & 8 for Exec A

=SUMPRODUCT(($A$1:$A$20>=TIME(7,0,0))*($A$1:$A$20<TIME(8,0,0))*($B$1:$B$20="
A"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

this can be easily done in one step using a pivot table. select your data,
then go Data=>Pivot Table.

Have the executives ID/Name as your column field and the time as your row
field. Put Exec in the data area and it will default to count of Excel.
then finish up (if you have xl2000 or later, this screen in on the last
dialog, under layout button).

Now click in the list of time, right click and select group and outline.
For group, choose just hour and set the start to 0:00, clear end.

This should give you what you want.

 

[Rajesh Sharma]

Thanx Tom,
i got better results in Pivot Table than I cud ever get with countif .
I will implement it rite away, In case I wish to generate a weekly
report, wat else do I add for Datewise, hourly call breakup.

Warm regards,
Rajesh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

If you have dates and times in one column, you can group on weeks and hours
I believe. If the dates are in a separate column, then group each of the
columns separately.