I have a list of agents and the times they have logged in and out of their phones, Some of them log out for a split shift, the problem I am having is to calculate their splits, I tried using Vlookup("Split",PASTE_IDLE_HERE!AU & (MATCH(D4,PASTE_IDLE_HERE!B:B,0)):AV & (MATCH(D4,PASTE_IDLE_HERE!B:B,0)+12),2,FALSE), but excel doesnt like that.
I need the formula to do 3 things,
1. Find the starting row for the agent in question:
=MATCH(D4,Paste_IDLE_here!B:B,0) 'Finds agent by extension
2. Find out how many entries we have for the agent in question:
=COUNTIF(Paste_IDLE_here!B:B,D4) 'Finds number of entries by extension
3. Look in column AU for the word split, if found report back time in column AV:
=VLOOKUP("Split",Paste_IDLE_here!AU:AV,2,FALSE)
The Vlookup works fine, except it pulls the first Split it finds and not the one for the agent that i want. Agents extension is in D4 fyi. I'm sure there is a better way to do this, I just can't seem to figure out what it is.
The agent fields look like:
The time report looks like this:
thanks in advance.
I need the formula to do 3 things,
1. Find the starting row for the agent in question:
=MATCH(D4,Paste_IDLE_here!B:B,0) 'Finds agent by extension
2. Find out how many entries we have for the agent in question:
=COUNTIF(Paste_IDLE_here!B:B,D4) 'Finds number of entries by extension
3. Look in column AU for the word split, if found report back time in column AV:
=VLOOKUP("Split",Paste_IDLE_here!AU:AV,2,FALSE)
The Vlookup works fine, except it pulls the first Split it finds and not the one for the agent that i want. Agents extension is in D4 fyi. I'm sure there is a better way to do this, I just can't seem to figure out what it is.
The agent fields look like:
| Daily Aspect v5.4.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 35 | Tue | DOE,JOHN | -MTWRF-&9AM-1PM/5PM-9PM | 6802 | ||
Daily_Report_To_Print | ||||||
The time report looks like this:
| Daily Aspect v5.4.xls | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Ext. | AgentName | Date | TypeofEvent | EventCode | EventDescription | Time | EventDuration | WorkSplit | SplitTime | ||
| 2 | 6802 | Doe,John | August24,2004 | Signon | 8:59:09AM | 0.00 | NoSplit | |||||
| 3 | 6802 | Doe,John | August24,2004 | Idle | 8:59:17AM | 0.13 | NoSplit | |||||
| 4 | 6802 | Doe,John | August24,2004 | Idle | 20.00 | HEALTHBREAK | 11:57:11AM | 2.87 | NoSplit | |||
| 5 | 6802 | Doe,John | August24,2004 | Idle | 1.00 | 1stBREAK | 12:54:45PM | 14.12 | NoSplit | |||
| 6 | 6802 | Doe,John | August24,2004 | Idle | 27.00 | IDLE-RESEARCH | 1:29:46PM | 17.42 | NoSplit | |||
| 7 | 6802 | Doe,John | August24,2004 | Idle | 3:57:15PM | 1.55 | NoSplit | |||||
| 8 | 6802 | Doe,John | August24,2004 | Signoff | 3:57:15PM | 0.00 | Split | 1:01 | ||||
| 9 | 6802 | Doe,John | August24,2004 | Signon | 4:59:14PM | 0.00 | NoSplit | |||||
| 10 | 6802 | Doe,John | August24,2004 | Idle | 4:59:19PM | 0.08 | NoSplit | |||||
| 11 | 6802 | Doe,John | August24,2004 | Idle | 7:23:55PM | 0.08 | NoSplit | |||||
| 12 | 6802 | Doe,John | August24,2004 | Idle | 47.00 | 2ndBREAK | 8:41:57PM | 14.55 | NoSplit | |||
| 13 | 6802 | Doe,John | August24,2004 | Signoff | 9:39:09PM | 0.00 | NoSplit | |||||
Sheet16 | ||||||||||||
thanks in advance.
