soopafly81
New Member
- Joined
- Jan 29, 2021
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi everyone! First time poster here after attempting to tackle my issue over several days.
I've included a range of sample data below to help understand what I am trying to achieve.
I've included a range of sample data below to help understand what I am trying to achieve.
- I have a list of case numbers (column A) which are duplicated because individuals log separate instances of time to those cases (columns F - I).
- I need my information organized by month which I will use the Date/Time Opened (column C)
- This is where it gets tricky:
- If for the month of December 2020, a person's name appears in the Assigned To field, I need to count the distinct number of case numbers for which they will get credit for working on so I can see the total number of cases worked in a month
- I also need to count the # of minutes from column G for only the time that was created by a specific individual and calculate the total time, independent of whether they were the Assigned To individual or not.
- For example, in the sample data set, Bonnie was assigned to 2 cases, so 2 would be the total.
- However, Bonnie has case time on other cases she wasn't the Assigned To person, which would need to add to her total of Time Spent (Min) which for this data set equals 75.
report1611928294552.xls | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Case Number | Case Owner | Case Close Date | Date/Time Opened | Assigned To | Case Time: Record Type | Case Time: Created By | Case Time: Created Date | Time Spent (Min) | ||
2 | 00047563 | Stephanie | 1/4/2021 7:14 | 12/31/2020 16:37 | Stephanie | 02 - Case Work | Stephanie | 1/4/2021 | 7 | ||
3 | 00047558 | John | 12/31/2020 16:06 | John | 02 - Case Work | Bonnie | 1/27/2021 | 10 | |||
4 | 00047558 | John | 12/31/2020 16:06 | John | 02 - Case Work | Bonnie | 1/27/2021 | 15 | |||
5 | 00047558 | John | 12/31/2020 16:06 | John | 02 - Case Work | John | 1/27/2021 | 30 | |||
6 | 00047557 | Gary | 1/4/2021 9:46 | 12/31/2020 16:02 | Gary | 02 - Case Work | Gary | 1/4/2021 | 30 | ||
7 | 00047556 | Linda | 1/6/2021 9:23 | 12/31/2020 16:02 | Linda | 02 - Case Work | Linda | 12/31/2020 | 4 | ||
8 | 00047555 | Laura | 1/22/2021 8:39 | 12/31/2020 15:52 | Laura | 01 - Case Management | Laura | 1/4/2021 | 20 | ||
9 | 00047550 | Stephanie | 12/31/2020 15:09 | Stephanie | 01 - Case Management | Stephanie | 1/27/2021 | 15 | |||
10 | 00047550 | Stephanie | 12/31/2020 15:09 | Stephanie | 01 - Case Management | Stephanie | 1/20/2021 | 10 | |||
11 | 00047549 | Stephanie | 1/5/2021 7:04 | 12/31/2020 15:02 | Stephanie | 02 - Case Work | Stephanie | 1/4/2021 | 10 | ||
12 | 00047546 | Kristy | 1/5/2021 15:47 | 12/31/2020 14:40 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 180 | ||
13 | 00047546 | Kristy | 1/5/2021 15:47 | 12/31/2020 14:40 | Kristy | 02 - Case Work | Kristy | 1/5/2021 | 165 | ||
14 | 00047546 | Kristy | 1/5/2021 15:47 | 12/31/2020 14:40 | Kristy | 02 - Case Work | Kristy | 1/5/2021 | 25 | ||
15 | 00047543 | Dina | 1/5/2021 11:14 | 12/31/2020 14:05 | Dina | 02 - Case Work | Dina | 1/4/2021 | 90 | ||
16 | 00047543 | Dina | 1/5/2021 11:14 | 12/31/2020 14:05 | Dina | 02 - Case Work | Dina | 1/5/2021 | 45 | ||
17 | 00047543 | Dina | 1/5/2021 11:14 | 12/31/2020 14:05 | Dina | 01 - Case Management | Dina | 1/4/2021 | 10 | ||
18 | 00047538 | Gary | 12/31/2020 14:46 | 12/31/2020 12:09 | Gary | 02 - Case Work | Gary | 12/31/2020 | 45 | ||
19 | 00047537 | Linda | 1/7/2021 11:04 | 12/31/2020 11:58 | Linda | 02 - Case Work | Linda | 1/4/2021 | 30 | ||
20 | 00047531 | Kristy | 1/4/2021 10:21 | 12/31/2020 11:10 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 10 | ||
21 | 00047531 | Kristy | 1/4/2021 10:21 | 12/31/2020 11:10 | Kristy | 01 - Case Management | Kristy | 1/4/2021 | 3 | ||
22 | 00047526 | Jessica | 1/22/2021 13:19 | 12/31/2020 10:44 | Jessica | 01 - Case Management | Jessica | 12/31/2020 | 5 | ||
23 | 00047518 | Gary | 1/4/2021 15:33 | 12/31/2020 10:21 | Gary | 02 - Case Work | Susan | 1/4/2021 | 45 | ||
24 | 00047515 | Laura | 1/4/2021 9:00 | 12/31/2020 10:01 | Laura | 02 - Case Work | Laura | 12/31/2020 | 8 | ||
25 | 00047514 | Laura | 1/4/2021 8:47 | 12/31/2020 9:58 | Laura | 02 - Case Work | Laura | 12/31/2020 | 15 | ||
26 | 00047512 | Linda | 1/7/2021 13:33 | 12/31/2020 9:39 | Kenneth | 01 - Case Management | Kenneth | 1/6/2021 | 10 | ||
27 | 00047512 | Linda | 1/7/2021 13:33 | 12/31/2020 9:39 | Kenneth | 02 - Case Work | Kenneth | 1/6/2021 | 115 | ||
28 | 00047512 | Linda | 1/7/2021 13:33 | 12/31/2020 9:39 | Kenneth | 02 - Case Work | Kenneth | 1/7/2021 | 35 | ||
29 | 00047512 | Linda | 1/7/2021 13:33 | 12/31/2020 9:39 | Kenneth | 01 - Case Management | Kenneth | 1/7/2021 | 5 | ||
30 | 00047508 | Dina | 12/31/2020 9:07 | 12/31/2020 8:51 | Dina | 01 - Case Management | Dina | 12/31/2020 | 10 | ||
31 | 00047479 | John | 1/25/2021 12:30 | 12/30/2020 20:49 | Christine | 02 - Case Work | Christine | 1/7/2021 | 65 | ||
32 | 00047478 | John | 1/25/2021 12:31 | 12/30/2020 20:48 | Christine | 02 - Case Work | Christine | 1/7/2021 | 110 | ||
33 | 00047471 | John | 12/31/2020 16:24 | 12/30/2020 19:08 | John | 02 - Case Work | John | 12/31/2020 | 10 | ||
34 | 00047468 | Dina | 1/4/2021 10:43 | 12/30/2020 17:48 | Dina | 02 - Case Work | Dina | 1/4/2021 | 5 | ||
35 | 00047468 | Dina | 1/4/2021 10:43 | 12/30/2020 17:48 | Dina | 01 - Case Management | Dina | 12/30/2020 | |||
36 | 00047466 | Stephanie | 1/4/2021 12:59 | 12/30/2020 17:33 | Stephanie | 02 - Case Work | Stephanie | 1/4/2021 | 15 | ||
37 | 00047464 | Linda | 1/7/2021 10:54 | 12/30/2020 17:12 | Linda | 02 - Case Work | Linda | 1/4/2021 | 30 | ||
38 | 00047462 | Lindsay | 12/30/2020 16:56 | Lindsay | 02 - Case Work | Lindsay | 1/28/2021 | 30 | |||
39 | 00047462 | Lindsay | 12/30/2020 16:56 | Lindsay | 02 - Case Work | Lindsay | 1/28/2021 | 90 | |||
40 | 00047462 | Lindsay | 12/30/2020 16:56 | Lindsay | 02 - Case Work | Lindsay | 1/19/2021 | 75 | |||
41 | 00047462 | Lindsay | 12/30/2020 16:56 | Lindsay | 02 - Case Work | Lindsay | 1/18/2021 | 45 | |||
42 | 00047461 | Stephanie | 12/31/2020 13:04 | 12/30/2020 16:52 | Stephanie | 01 - Case Management | Stephanie | 12/30/2020 | 5 | ||
43 | 00047460 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:49 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 3 | ||
44 | 00047460 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:49 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 10 | ||
45 | 00047460 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:49 | Kristy | 01 - Case Management | Kristy | 1/4/2021 | 3 | ||
46 | 00047458 | John | 12/30/2020 16:46 | John | 02 - Case Work | Bonnie | 1/27/2021 | 15 | |||
47 | 00047458 | John | 12/30/2020 16:46 | John | 03 - Case Check | Bonnie | 1/27/2021 | 10 | |||
48 | 00047456 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:40 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 3 | ||
49 | 00047456 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:40 | Kristy | 01 - Case Management | Kristy | 12/31/2020 | 10 | ||
50 | 00047456 | Kristy | 1/5/2021 10:08 | 12/30/2020 16:40 | Kristy | 01 - Case Management | Kristy | 1/4/2021 | 3 | ||
51 | 00047453 | Laura | 1/7/2021 9:07 | 12/30/2020 16:12 | Laura | 02 - Case Work | Laura | 1/4/2021 | 20 | ||
52 | 00047452 | Dina | 1/4/2021 11:01 | 12/30/2020 16:12 | Dina | 01 - Case Management | Dina | 12/30/2020 | 5 | ||
53 | 00047452 | Dina | 1/4/2021 11:01 | 12/30/2020 16:12 | Dina | 02 - Case Work | Dina | 1/4/2021 | 15 | ||
54 | 00047449 | Gary | 12/30/2020 20:04 | 12/30/2020 16:02 | Gary | 02 - Case Work | Gary | 12/30/2020 | 20 | ||
55 | 00047444 | Linda | 1/4/2021 10:30 | 12/30/2020 15:37 | Linda | 02 - Case Work | Linda | 1/4/2021 | 30 | ||
56 | 00047440 | Lindsay | 12/30/2020 15:17 | Lindsay | 02 - Case Work | Lindsay | 1/28/2021 | 60 | |||
57 | 00047440 | Lindsay | 12/30/2020 15:17 | Lindsay | 02 - Case Work | Lindsay | 1/28/2021 | 70 | |||
58 | 00047440 | Lindsay | 12/30/2020 15:17 | Lindsay | 02 - Case Work | Lindsay | 1/18/2021 | 45 | |||
59 | 00047433 | Gary | 12/31/2020 9:56 | 12/30/2020 14:40 | Gary | 01 - Case Management | Gary | 12/31/2020 | 5 | ||
60 | 00047430 | Lindsay | 1/4/2021 11:22 | 12/30/2020 14:26 | Lindsay | 02 - Case Work | Lindsay | 12/31/2020 | 5 | ||
61 | 00047429 | Dina | 12/30/2020 20:49 | 12/30/2020 14:26 | Dina | 01 - Case Management | Dina | 12/30/2020 | 5 | ||
62 | 00047427 | John | 1/25/2021 12:30 | 12/30/2020 14:22 | Christine | 02 - Case Work | Christine | 1/22/2021 | 180 | ||
63 | 00047427 | John | 1/25/2021 12:30 | 12/30/2020 14:22 | Christine | 02 - Case Work | Christine | 1/7/2021 | 110 | ||
64 | 00047415 | Laura | 1/4/2021 11:44 | 12/30/2020 12:33 | Laura | 02 - Case Work | Laura | 12/31/2020 | 30 | ||
65 | 00047414 | Gary | 12/31/2020 14:46 | 12/30/2020 12:31 | Gary | 02 - Case Work | Gary | 12/31/2020 | 10 | ||
66 | 00047405 | Linda | 1/7/2021 16:07 | 12/30/2020 11:31 | Linda | 02 - Case Work | Linda | 1/7/2021 | 15 | ||
67 | 00047402 | Gary | 12/30/2020 19:48 | 12/30/2020 11:18 | Gary | 02 - Case Work | Gary | 12/30/2020 | 20 | ||
68 | 00047399 | John | 12/30/2020 13:54 | 12/30/2020 11:09 | John | 02 - Case Work | John | 12/30/2020 | 30 | ||
69 | 00047397 | Dina | 1/21/2021 12:31 | 12/30/2020 10:58 | Dina | 02 - Case Work | Dina | 1/6/2021 | 5 | ||
70 | 00047397 | Dina | 1/21/2021 12:31 | 12/30/2020 10:58 | Dina | 01 - Case Management | Dina | 12/30/2020 | 20 | ||
71 | 00047397 | Dina | 1/21/2021 12:31 | 12/30/2020 10:58 | Dina | 01 - Case Management | Dina | 1/6/2021 | 5 | ||
72 | 00047397 | Dina | 1/21/2021 12:31 | 12/30/2020 10:58 | Dina | 01 - Case Management | Dina | 1/21/2021 | 5 | ||
73 | 00047393 | Bonnie | 12/31/2020 11:19 | 12/30/2020 10:43 | Bonnie | 02 - Case Work | Bonnie | 12/31/2020 | 15 | ||
74 | 00047387 | Dina | 1/5/2021 15:07 | 12/30/2020 10:01 | Adele | 02 - Case Work | Adele | 1/5/2021 | 60 | ||
75 | 00047387 | Dina | 1/5/2021 15:07 | 12/30/2020 10:01 | Adele | 02 - Case Work | Adele | 1/5/2021 | 5 | ||
76 | 00047387 | Dina | 1/5/2021 15:07 | 12/30/2020 10:01 | Adele | 02 - Case Work | Adele | 1/5/2021 | 5 | ||
77 | 00047387 | Dina | 1/5/2021 15:07 | 12/30/2020 10:01 | Adele | 02 - Case Work | Adele | 1/5/2021 | 5 | ||
78 | 00047387 | Dina | 1/5/2021 15:07 | 12/30/2020 10:01 | Adele | 02 - Case Work | Adele | 12/30/2020 | 30 | ||
79 | 00047382 | Gary | 12/30/2020 10:25 | 12/30/2020 9:24 | Gary | 02 - Case Work | Gary | 12/30/2020 | 15 | ||
80 | 00047381 | John | 12/30/2020 13:55 | 12/30/2020 9:23 | John | 02 - Case Work | John | 12/30/2020 | 10 | ||
81 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 01 - Case Management | Stephanie | 12/30/2020 | 7 | ||
82 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 02 - Case Work | Erika | 1/6/2021 | 180 | ||
83 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 02 - Case Work | Erika | 1/6/2021 | 165 | ||
84 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 01 - Case Management | Stephanie | 1/7/2021 | 5 | ||
85 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 02 - Case Work | Erika | 1/4/2021 | 210 | ||
86 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 02 - Case Work | Erika | 1/8/2021 | 40 | ||
87 | 00047372 | Stephanie | 1/8/2021 10:16 | 12/30/2020 8:26 | Erika | 02 - Case Work | Erika | 1/1/2021 | 75 | ||
88 | 00047364 | Linda | 12/30/2020 9:25 | 12/30/2020 6:30 | Bonnie | 02 - Case Work | Bonnie | 12/30/2020 | 10 | ||
report1611928294552 |