J
James
I'm trying to determine the number of calls a 24/7 call center takes per
person in their first hour and their last hour. I'm trying to build logic for
the 7 different time ranges (pre-ot, early, 1st hr, btw hr, last hr, late,
post ot) and while QC'ing my work I found that some of the equations give
wrong info. Some agents stay overnight so their start time and end time are
on different reported dates.
Login ID Date Time Calls
Doe, J 2/28/2007 12:00 AM 12:30 AM 1
Doe, J 2/28/2007 12:30 AM 1:00 AM 0
Doe, J 2/28/2007 1:00 AM 1:30 AM 0
Doe, J 2/28/2007 1:30 AM 2:00 AM 0
Doe, J 2/28/2007 2:00 AM 2:30 AM 2
Doe, J 2/28/2007 2:30 AM 3:00 AM 0
Doe, J 2/28/2007 3:00 AM 3:30 AM 1 - last hr
Doe, J 2/28/2007 3:30 AM 4:00 AM 0 - last hr
Doe, J 2/28/2007 4:00 AM 4:30 AM 1 - late logout
Doe, J 2/28/2007 6:30 PM 7:00 PM 0 - early
Doe, J 2/28/2007 7:00 PM 7:30 PM 0 -1st hour
Doe, J 2/28/2007 7:30 PM 8:00 PM 1 -1st hour*
Doe, J 2/28/2007 8:00 PM 8:30 PM 1
Doe, J 2/28/2007 8:30 PM 9:00 PM 1
Doe, J 2/28/2007 9:00 PM 9:30 PM 0
Doe, J 2/28/2007 9:30 PM 10:00 PM 2
Doe, J 2/28/2007 10:00 PM 10:30 PM 1
Doe, J 2/28/2007 10:30 PM 11:00 PM 0
Doe, J 2/28/2007 11:00 PM 11:30 PM 1
Doe, J 2/28/2007 11:30 PM 12:00 AM 1*
=SUMPRODUCT(--(($A$2:$A$65536)=$L1),--(($B$2:$B$65536)=Q$1),--(($C$2:$C$65536>=$M1)),--(($D$2:$D$65536)<=$N1),(E$2:$E$65536))
where
A-L is the name comparison, B-Q is the date comparison, C-M is the 1st hour
start, D-N is the 1st hour end (plus one hour), E is sum of calls
For this particular agent the sum product produces 2 (the calls w/ *). If I
change it to be D<N instead of D<=N it only counts the 11:30p-12:00p hour.
How to I correct this to get the right data?
FYI this equation works for 90% of the others.
person in their first hour and their last hour. I'm trying to build logic for
the 7 different time ranges (pre-ot, early, 1st hr, btw hr, last hr, late,
post ot) and while QC'ing my work I found that some of the equations give
wrong info. Some agents stay overnight so their start time and end time are
on different reported dates.
Login ID Date Time Calls
Doe, J 2/28/2007 12:00 AM 12:30 AM 1
Doe, J 2/28/2007 12:30 AM 1:00 AM 0
Doe, J 2/28/2007 1:00 AM 1:30 AM 0
Doe, J 2/28/2007 1:30 AM 2:00 AM 0
Doe, J 2/28/2007 2:00 AM 2:30 AM 2
Doe, J 2/28/2007 2:30 AM 3:00 AM 0
Doe, J 2/28/2007 3:00 AM 3:30 AM 1 - last hr
Doe, J 2/28/2007 3:30 AM 4:00 AM 0 - last hr
Doe, J 2/28/2007 4:00 AM 4:30 AM 1 - late logout
Doe, J 2/28/2007 6:30 PM 7:00 PM 0 - early
Doe, J 2/28/2007 7:00 PM 7:30 PM 0 -1st hour
Doe, J 2/28/2007 7:30 PM 8:00 PM 1 -1st hour*
Doe, J 2/28/2007 8:00 PM 8:30 PM 1
Doe, J 2/28/2007 8:30 PM 9:00 PM 1
Doe, J 2/28/2007 9:00 PM 9:30 PM 0
Doe, J 2/28/2007 9:30 PM 10:00 PM 2
Doe, J 2/28/2007 10:00 PM 10:30 PM 1
Doe, J 2/28/2007 10:30 PM 11:00 PM 0
Doe, J 2/28/2007 11:00 PM 11:30 PM 1
Doe, J 2/28/2007 11:30 PM 12:00 AM 1*
=SUMPRODUCT(--(($A$2:$A$65536)=$L1),--(($B$2:$B$65536)=Q$1),--(($C$2:$C$65536>=$M1)),--(($D$2:$D$65536)<=$N1),(E$2:$E$65536))
where
A-L is the name comparison, B-Q is the date comparison, C-M is the 1st hour
start, D-N is the 1st hour end (plus one hour), E is sum of calls
For this particular agent the sum product produces 2 (the calls w/ *). If I
change it to be D<N instead of D<=N it only counts the 11:30p-12:00p hour.
How to I correct this to get the right data?
FYI this equation works for 90% of the others.