Hi i have a tedious job of taking clock-in data from a call center and
reporting back how many calls were taken in their first and last hour. I'm
currently doing this manually but there has to be a faster more effeicient
way to do this.

The data is in this format:

Name - Date - Time1 - Time2 - Calls - Staffed Time
J. Doe - 3/12 - 7:00 AM - 7:30 AM - 0 - 2:30 (logged in 2:30 min early)
J. Doe - 3/12 - 7:30 AM - 8:00 AM - 1 - 30:00
J. Doe - 3/12 - 8:00 AM - 8:30 AM - 3 - 30:00
etc.

I have another set of data with their name and shift time that I can compare
to. There are 400+ agents and the fun part is that they sometimes come in
early or stay late for OT. I would also like to break that data out into a
different cell. Note that I would not need to report how many calls were
taken between their first and last hour. Additionally if J. Doe took a call
in the 2:30min he was logged in early, that is also part of the first hour,
and the agents have different start times that include extended (ie
graveyard) hours (the start of their data is on one sheet and the other data
is on the second sheet with the next day shifts)

My thought was to do something similar to Vlookup, with if-then statements.
For example:
If Name=J.Doe and Time1 = Start_Time, then (report back the sum of calls)

I tried with Vlookup and it won't take an if statement in the formula. My
excel guru's around here are stumped too. Is there another function that will
produce the same result? The reported data will need to be a list of: agent
name, date, number of calls taken first hour, last hour.

Thanks
--
James