Times
Call ID Status Type Assigned Resolved Database ID Name
754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
Times
This is how I would do it: Make a column of dates. I used I2:I84 for 3/7/2008
through 5/28/2008 (adjust for the time period you actually care about. Next to this column, I used a formula that tells me how many items dschanbe is working on at that date: In J2 I used =SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15)) and copied down, where column D is Assigned, E is Resolved, and F is Database ID. Here you could replace "DSCHNABE" with another name or a reference to the name. Now that that's done, there are several options. You could nest it inside an IF statement, =IF(SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15))5,"Max Adherence Problem",""), or use a chart, or conditional formatting. It really depends on what you're trying to do and your preference. I would use a chart in this example. If your data contains most Database ID's than just DSCHABE, you could use one column for each, and these would be one line on a line graph. Any time the line crossed 5, you would have your problem. "sed" wrote: Call ID Status Type Assigned Resolved Database ID Name 754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
Times
Thank you so much!
-- sss "sb1920alk" wrote: This is how I would do it: Make a column of dates. I used I2:I84 for 3/7/2008 through 5/28/2008 (adjust for the time period you actually care about. Next to this column, I used a formula that tells me how many items dschanbe is working on at that date: In J2 I used =SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15)) and copied down, where column D is Assigned, E is Resolved, and F is Database ID. Here you could replace "DSCHNABE" with another name or a reference to the name. Now that that's done, there are several options. You could nest it inside an IF statement, =IF(SUMPRODUCT(--(I2=$D$2:$D$15),--(I2<=$E$2:$E$15),--("DSCHNABE"=$F$2:$F$15))5,"Max Adherence Problem",""), or use a chart, or conditional formatting. It really depends on what you're trying to do and your preference. I would use a chart in this example. If your data contains most Database ID's than just DSCHABE, you could use one column for each, and these would be one line on a line graph. Any time the line crossed 5, you would have your problem. "sed" wrote: Call ID Status Type Assigned Resolved Database ID Name 754776 Closed Problem 05/22/08 05/22/08 dschnabe MFGSYS 751502 Closed Problem 05/09/08 05/09/08 dschnabe SAP_MFG 747345 Closed Problem 04/21/08 04/21/08 dschnabe MFGSYS 746533 Closed Problem 04/16/08 04/18/08 dschnabe MFGSYS 746472 Closed Problem 04/16/08 04/17/08 dschnabe SAP_MFG 745730 Closed Problem 04/14/08 04/15/08 dschnabe MFGSYS 741597 Closed Problem 03/26/08 03/27/08 dschnabe SAP_MFG 738441 Closed Problem 03/12/08 03/18/08 dschnabe SAP_MFG 738424 Closed Problem 03/12/08 03/13/08 dschnabe SAP_MFG 737777 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737773 Closed Problem 03/10/08 03/11/08 dschnabe MFGSYS 737271 Closed Problem 03/07/08 03/10/08 dschnabe MFGSYS 737253 Closed Problem 03/07/08 03/07/08 dschnabe MFGSYS 755669 Closed Problem 05/27/08 05/28/08 dschnabe MFGSYS How can I determine if user DSCHNABE has more than 5 CALLID's in the same period of time? ex. if he has 7 call id's open on 03/27/08 then "Max Adherence Problem" |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com