Hello,
I hope I'm doing this correctly.
Let me set the stage first.
I'm trying to make a report more automated so that when I'm out of the office, it is less of a burden to do the report.
Here is a copy of the section that I'm trying to automate:
I use the following formula in cell a1 to get the date of the previous business day: =IF(WEEKDAY(B6)=6,B6-3,B6-1)
Next, I go to a different excel spreadsheet and filter the data to the previous business day; and then locate the last entry ( or latest hour) of data and copy and paste the information into the spreadsheet that will be sent out ( the snap shot above). Each group has its own section, so I go into each section and copy & paste. Please see below for example of one of the data sources:
At the moment, I'm stuck on how to retrieve the latest time in the Time (AZ) column ( i.e. 5:05 pm AZ time) from the data source. I tried using =MAX(Sheet2!C:C) but since the data source spreadsheet has been in use for a while, it pulls a time much later than 5:05 pm. I've tried using a vlookup with an If statement, an if statement with a MAX statement, a vlookup with a MAX statement; and usually get a False return or 0:00 or run into the message of too many/few arguments or another message that basically says that I did something wrong.
Any assistance is much appreciated.
I hope that my question makes sense to all.
Thank you,
RMJ
I hope I'm doing this correctly.
Let me set the stage first.
I'm trying to make a report more automated so that when I'm out of the office, it is less of a burden to do the report.
Here is a copy of the section that I'm trying to automate:
07/27/20 | ||||||||||
| Name | Time(AZ) | EST Time | Pen: | Int | # of Accts | Calls Connected | Agents Logged In | Aban OB/Rate | Offered IB | Aban IB/Rate |
| Early | 5:05 PM | 8:05 PM | 100% | 249% | 1021 | 137(4.76%) | 1 | 11 (8.02%) | 39 | 0(0.00%) |
| Mid-Stage | 5:05 PM | 8:05 PM | 99% | 250% | 934 | 228(8.50%) | 1 | 26(11.40%) | 190 | 16 (8.42%) |
| Specialty | 5:05 PM | 8:05 PM | 83% | 83% | 263 | 141 (43.38%) | 0 | 0(0.00%) | 70 | 8(11.42%) |
7/28/2020 |
Next, I go to a different excel spreadsheet and filter the data to the previous business day; and then locate the last entry ( or latest hour) of data and copy and paste the information into the spreadsheet that will be sent out ( the snap shot above). Each group has its own section, so I go into each section and copy & paste. Please see below for example of one of the data sources:
| Date | Name | Time (AZ) | EST Time | Pen: | Int: | # of Accts | Calls Connected | Agents logged in | Aban OB Count/ Rate | Offered IB | Aban IB Count/Rate |
20-Jul | Mid Stage | 8:15 AM | 11:15 AM | 59% | 63% | 1055 | 53(7.49%) | 17 | 2(3.77%) | 5 | 0(0%) |
20-Jul | Mid Stage | 10:25 AM | 1:25 PM | 88% | 121% | 1055 | 107 (8.29%) | 19 | 3 (2.80%) | 10 | 1 (10.00%) |
20-Jul | Mid Stage | 12:30 PM | 3:30 PM | 98% | 154% | 1055 | 114 (6.94%) | 19 | 3 (2.63%) | 21 | 4 (19.04%) |
20-Jul | Mid Stage | 2:40 PM | 5:40 PM | 100% | 194% | 1055 | 123 (5.87%) | 17 | 3 (2.43%) | 31 | 4 (12.90%) |
20-Jul | Mid Stage | 5:10 PM | 8:10 PM | 100% | 196% | 1055 | 123 (5.87%) | 0 | 3 (2.43%) | 34 | 4 (11.76%) |
21-Jul | Mid Stage | 8:16 AM | 11:16 AM | 58% | 67% | 1031 | 53(7.52%) | 12 | 5(9.43%) | 2 | 0(0%) |
21-Jul | Mid Stage | 11:35 AM | 2:35 PM | 99% | 166% | 1031 | 108 (6.20%) | 16 | 10 (9.25%) | 10 | 1 (10.00%) |
21-Jul | Mid Stage | 1:40 PM | 4:40 PM | 100% | 314% | 1031 | 142 (4.48%) | 11 | 12 (8.45%) | 14 | 1 (7.14%) |
21-Jul | Mid Stage | 3:10 PM | 6:10 PM | 100% | 315% | 1031 | 146 (4.59%) | 4 | 12 (8.21%) | 18 | 3 (16.66%) |
21-Jul | Mid Stage | 5:30 PM | 8:30 PM | 100% | 315% | 1031 | 146 (4.59%) | 0 | 12 (8.21%) | 22 | 3 (13.63%) |
22-Jul | Mid Stage | 8:26 AM | 11:26 AM | 68% | 74% | 1013 | 52 (6.85%) | 16 | 1(1.92%) | 3 | 0(0%) |
22-Jul | Mid Stage | 10:30 AM | 1:30 PM | 98% | 148% | 1013 | 98 (6.56%) | 17 | 1 (1.02%) | 8 | 0 (0.00%) |
22-Jul | Mid Stage | 12:30 PM | 3:30 PM | 99% | 257% | 1013 | 132 (5.07%) | 15 | 1 (0.07%) | 15 | 0 (0.00%) |
22-Jul | Mid Stage | 3:25 PM | 6:25 PM | 100% | 315% | 1013 | 148 (4.72%) | 6 | 1 (0.06%) | 22 | 0 (0.00%) |
22-Jul | Mid Stage | 5:20 PM | 8:20 PM | 100% | 315% | 1013 | 148 (4.72%) | 0 | 1 (0.06%) | 23 | 1 (4.34%) |
23-Jul | Mid Stage | 8:32 AM | 11:32 AM | 63% | 77% | 993 | 46(5.37%) | 16 | 5(10.41%) | 6 | 0(0%) |
23-Jul | Mid Stage | 10:45 AM | 1:45 PM | 83% | 115% | 993 | 78 (6.70%) | 15 | 14 (17.94%) | 11 | 0 (0.00%) |
23-Jul | Mid Stage | 12:30 PM | 3:30 PM | 98% | 151% | 993 | 90 (5.89%) | 14 | 16 (17.77%) | 21 | 0 (0.00%) |
23-Jul | Mid Stage | 2:30 PM | 5:30 PM | 100% | 236% | 993 | 115 (4.72%) | 19 | 17 (14.78%) | 26 | 0 (0.00%) |
23-Jul | Mid Stage | 5:25 PM | 8:25 PM | 100% | 278% | 993 | 122 (3.52%) | 0 | 17 (13.93%) | 34 | 0 (0.00%) |
24-Jul | Mid Stage | 8:58 AM | 11:58 AM | 80% | 108% | 967 | 57(5.34%) | 17 | 4(6.89%) | 2 | 0 (0.00%) |
24-Jul | Mid Stage | 12:15 PM | 3:15 PM | 99% | 223% | 967 | 103 (4.77%) | 18 | 7 (6.79%) | 4 | 0 (0.00%) |
24-Jul | Mid Stage | 2:10 PM | 5:10 PM | 100% | 298% | 967 | 118 (4.03%) | 16 | 7 (5.93%) | 9 | 0 (0.00%) |
24-Jul | Mid Stage | 5:00 PM | 8:00 PM | 100% | 311% | 967 | 119 (3.81%) | 0 | 7 (5.88%) | 9 | 0 (0.00%) |
27-Jul | Mid Stage | 8:49 AM | 11:49 AM | 57% | 57% | 1021 | 42 (6.95%) | 17 | 4 (9.52%) | 9 | 0 (0.00%) |
27-Jul | Mid Stage | 10:21 AM | 1:21 PM | 89% | 124% | 1021 | 65 (5.72%) | 18 | 4 (6.1%) | 16 | 0 (0.00%) |
27-Jul | Mid Stage | 12:35 PM | 3:35 PM | 98% | 145% | 1021 | 100 (6.60%) | 18 | 10 (10%) | 25 | 0 (0.00%) |
27-Jul | Mid Stage | 5:05 PM | 8:05 PM | 99% | 250% | 934 | 228(8.50%) | 1 | 26(11.40%) | 190 | 16 (8.42%) |
At the moment, I'm stuck on how to retrieve the latest time in the Time (AZ) column ( i.e. 5:05 pm AZ time) from the data source. I tried using =MAX(Sheet2!C:C) but since the data source spreadsheet has been in use for a while, it pulls a time much later than 5:05 pm. I've tried using a vlookup with an If statement, an if statement with a MAX statement, a vlookup with a MAX statement; and usually get a False return or 0:00 or run into the message of too many/few arguments or another message that basically says that I did something wrong.
Any assistance is much appreciated.
I hope that my question makes sense to all.
Thank you,
RMJ
