I have a table of data where I have records of time spent in a particular priority status of “Blocked” via Columns S through V
Sometimes there are multiple records per ticket number, sometimes there may be none for a ticket number where there is no record for that ticket number in this 1st table.
I’ve like to leverage this data, and reconcile this data against another table where I have times tickets have spent in a workflow status via Columns X through AQ. There will always be just 1 record per ticket number in this 2nd table
Basically I want to match on ticket number between Column S and Column X (note again that array S:V may have multiple records per ticket number), and if the start date time of the priority status in Column T is after the start date time of the Workflow status in Column AK but before the end date time of the Workflow status in Column AM, I’d like to subtract the time(s) of the ticket in being in a particular priority status (Column V) from the time it was in a particular workflow status (Column AQ).
If there are multiple records of a ticket number and it being in a particular priority status of Blocked, I’d like to sum that time, and then subtract that total from my time in a workflow status.
Here is some sample data from Columns S through V for my 1st table:
Here is some sample data from Columns X through AQ and my 2nd table:
Basically I want to enhance the calculation formula in Column AQ to subtract whatever time blocked in my 1st table when the ticket numbers match. Right now the time in a particular workflow status is just a simple subtract of the date/time in Column AQ from the date/time in Column AK. I want to factor in subtracting that time(s) blocked to my time in that Development workflow status as well.
Some examples, for row 37 of the 2nd table for ticket # EDGE-14199, my result before accounting for blocked time is 2.31 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14199 being blocked, the 1st record of it being blocked would NOT be subtracted because the date/time start of the block did not start before the date/time start of the workflow status of Development. The 2nd record if it being blocked would also NOT be subtracted because of the date/time start of the block did not start before the date/time start of the workflow status of Development. So my result would remain 2.31 in Column AQ.
For row 41 of the 2nd table for ticket # EDGE-14240, my result before accounting for blocked time is 4.07 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 1 record of EDGE-14199 being blocked, the record of it being blocked would NOT be subtracted because the date/time start of the block did not start before the date/time start of the workflow status of Development. So my result in Column AQ would remain 4.07.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 58 of the 2nd table for ticket # EDGE-14352, my result before accounting for blocked time is 12.90 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, ONLY ONE of the records of it being blocked WOULD be subtracted because date/time start of one of the block records did start before the date/time start of the workflow status of Development (the 1st record of it being blocked). That’s because the start time of the 1st block record (9/26 at 4:22PM) is after the date/time start of the Workflow status in Column N (9/22 at 2:45PM), and before the date/time end in Column S (10/5 12:25PM). The record of the ticket’s 2nd time blocked, the date/time start (10/5 3:51 PM) is after the date/time end in Column S (10/5 12:25PM). So after subtracting my blocked times of 3.71, my result in Column AQ would be about 9.19 rather than 12.90.
I’d also like to ensure there are no negative results, so if after the subtraction (if applicable) of time blocked, if the result is negative, then just want the result defaulted to zero.
Appreciate any help factoring in this indexing and subtracting of time blocked into my time spent calculation in the Development workflow status.
Sometimes there are multiple records per ticket number, sometimes there may be none for a ticket number where there is no record for that ticket number in this 1st table.
I’ve like to leverage this data, and reconcile this data against another table where I have times tickets have spent in a workflow status via Columns X through AQ. There will always be just 1 record per ticket number in this 2nd table
Basically I want to match on ticket number between Column S and Column X (note again that array S:V may have multiple records per ticket number), and if the start date time of the priority status in Column T is after the start date time of the Workflow status in Column AK but before the end date time of the Workflow status in Column AM, I’d like to subtract the time(s) of the ticket in being in a particular priority status (Column V) from the time it was in a particular workflow status (Column AQ).
If there are multiple records of a ticket number and it being in a particular priority status of Blocked, I’d like to sum that time, and then subtract that total from my time in a workflow status.
Here is some sample data from Columns S through V for my 1st table:
| Times Blocked | |||
| Ticket Number | Date/Time Block Started | Date/Time Block Ended | Time Blocked |
| EDGE-13905 | 7/6/22 9:18 AM | 7/18/22 8:03 AM | 11.95 |
| EDGE-13987 | 7/18/22 9:27 AM | n/a | |
| EDGE-14074 | 9/22/22 1:58 PM | 10/19/22 9:06 AM | 26.80 |
| EDGE-14199 | 8/23/22 8:25 AM | 8/23/22 8:31 AM | 0.00 |
| EDGE-14199 | 8/23/22 8:31 AM | 9/1/22 4:17 PM | 9.32 |
| EDGE-14240 | 10/10/22 5:38 PM | 10/12/22 11:45 AM | 1.76 |
| EDGE-14242 | 8/26/22 9:27 AM | 9/19/22 9:27 AM | 24.00 |
| EDGE-14336 | 10/10/22 9:09 AM | 10/19/22 9:27 AM | 9.01 |
| EDGE-14336 | 10/21/22 2:06 PM | 10/25/22 12:07 PM | 3.92 |
| EDGE-14352 | 9/26/22 4:22 PM | 9/30/22 9:24 AM | 3.71 |
| EDGE-14352 | 10/5/22 3:51 PM | 10/24/23 12:32 PM | 383.86 |
| EDGE-14366 | 10/14/22 9:25 AM | 10/21/22 9:20 AM | 7.00 |
| EDGE-14366 | 10/21/22 9:20 AM | 11/2/22 9:07 AM | 11.99 |
| EDGE-14380 | 9/26/22 4:24 PM | 9/30/22 7:46 AM | 3.64 |
| EDGE-14392 | 10/10/22 1:30 PM | 10/24/23 12:33 PM | 378.96 |
| EDGE-14410 | 10/12/22 12:09 PM | 10/12/22 4:43 PM | 0.19 |
| EDGE-14425 | 10/4/22 1:43 PM | 11/8/22 7:50 AM | 34.75 |
| EDGE-14427 | 10/19/22 4:56 PM | 11/2/22 9:07 AM | 13.67 |
| EDGE-14428 | 10/19/22 4:56 PM | 11/2/22 9:07 AM | 13.67 |
| EDGE-14534 | 10/25/22 1:31 PM | 10/25/22 1:31 PM | 0.00 |
| EDGE-14586 | 12/16/22 9:23 AM | 12/16/22 11:04 AM | 0.07 |
| EDGE-14589 | 10/5/23 9:17 AM | 10/12/23 11:53 AM | 7.11 |
| EDGE-14596 | 1/18/23 9:24 AM | 1/23/23 3:04 PM | 5.24 |
| EDGE-14603 | 12/15/22 9:21 AM | 1/3/23 8:57 AM | 18.98 |
| EDGE-14681 | 2/6/23 9:22 AM | 2/23/23 8:50 AM | 16.98 |
| EDGE-14805 | 12/16/22 7:32 AM | 12/21/22 9:26 AM | 5.08 |
| EDGE-14806 | 12/16/22 7:41 AM | 12/22/22 7:38 AM | 6.00 |
| EDGE-14854 | 1/5/23 1:42 PM | 2/9/23 7:46 AM | 34.75 |
| EDGE-14872 | 1/12/23 11:42 AM | 1/16/23 8:51 AM | 3.88 |
| EDGE-14941 | 1/19/23 10:55 AM | 1/19/23 1:16 PM | 0.10 |
| EDGE-14982 | 2/8/23 10:50 AM | 2/13/23 1:24 PM | 5.11 |
| EDGE-14982 | 2/15/23 9:33 AM | 2/16/23 9:31 AM | 1.00 |
| EDGE-14982 | 2/16/23 4:15 PM | 2/16/23 4:21 PM | 0.00 |
| EDGE-14982 | 2/20/23 9:19 AM | 2/22/23 9:19 AM | 2.00 |
| EDGE-14995 | 1/26/23 11:34 AM | 1/26/23 11:34 AM | 0.00 |
| EDGE-15013 | 2/8/23 10:20 AM | 10/24/23 12:34 PM | 258.09 |
| EDGE-15080 | 2/17/23 9:49 AM | 2/20/23 4:09 PM | 3.26 |
| EDGE-15136 | 8/29/23 10:29 AM | 10/23/23 9:54 AM | 54.98 |
| EDGE-15144 | 3/14/23 10:18 AM | 4/20/23 9:32 AM | 36.97 |
| EDGE-15161 | 3/16/23 2:06 PM | 4/3/23 9:38 AM | 17.81 |
| EDGE-15164 | 3/16/23 11:24 AM | 3/16/23 11:25 AM | 0.00 |
| EDGE-15210 | 3/17/23 9:20 AM | n/a | |
| EDGE-15210 | 3/17/23 9:20 AM | 10/24/23 12:11 PM | 221.12 |
| EDGE-15341 | 3/30/23 12:05 PM | n/a | |
| EDGE-15343 | 4/4/23 9:19 AM | n/a | |
| EDGE-15345 | 4/10/23 12:14 PM | n/a | |
| EDGE-15482 | 4/17/23 9:18 AM | 4/18/23 1:23 PM | 1.17 |
| EDGE-15482 | 4/19/23 3:19 PM | 4/25/23 9:21 AM | 5.75 |
| EDGE-15488 | 5/15/23 9:25 AM | 5/18/23 9:16 AM | 2.99 |
| EDGE-15566 | 4/14/23 2:55 PM | 5/1/23 10:42 AM | 16.82 |
Here is some sample data from Columns X through AQ and my 2nd table:
| Additional Details | Ticket started Gathering Requirements | Ticket Ready to Develop | Ticket started Development | Tickets finishing Development | |||||||||||||||
| Ticket Number | Fix Version | Story Points | Pairs | Spike (Y/N) | Rework after Code Review (Y/N) | Ticked failed QA | Status | Date/Time Status Changed | Status | Date/Time Status Changed | Time till Ready for Development | Status | Date/Time Status Changed | Developer Name (if available) | Date/Time Assignee Changed | recon | Status | Date/Time Status Changed | Time till Ready for Code Review |
| EDGE-13283 | |||||||||||||||||||
| EDGE-13293 | |||||||||||||||||||
| EDGE-13901 | |||||||||||||||||||
| EDGE-13904 | |||||||||||||||||||
| EDGE-13907 | |||||||||||||||||||
| EDGE-13908 | |||||||||||||||||||
| EDGE-13909 | Development | 7/5/22 1:08 PM | 6/29/22 10:33 AM | 6.11 | Ready for Code Review | 7/5/22 1:08 PM | 0.00 | ||||||||||||
| EDGE-13910 | |||||||||||||||||||
| EDGE-13952 | |||||||||||||||||||
| EDGE-13974 | Development | 10/5/22 8:27 AM | 10/4/22 9:21 AM | 0.96 | Ready for Code Review | 10/9/22 7:01 PM | 4.44 | ||||||||||||
| EDGE-13985 | Development | 9/26/22 3:31 PM | 9/26/22 1:45 PM | 0.07 | Ready for Code Review | 10/3/22 9:48 AM | 6.76 | ||||||||||||
| EDGE-14008 | Development | 7/29/22 9:39 AM | 7/29/22 9:38 AM | 0.00 | Ready for Code Review | 8/24/22 12:54 PM | 26.14 | ||||||||||||
| EDGE-14009 | Development | 7/29/22 9:39 AM | 7/29/22 9:39 AM | 0.00 | Ready for Code Review | 8/24/22 12:53 PM | 26.13 | ||||||||||||
| EDGE-14024 | Development | 11/8/22 2:15 PM | 11/8/22 2:16 PM | 0.00 | Ready for Code Review | 11/9/22 11:01 AM | 0.87 | ||||||||||||
| EDGE-14026 | Development | 7/18/22 4:52 PM | 7/18/22 9:58 AM | 0.29 | Ready for Code Review | 7/18/22 4:52 PM | 0.00 | ||||||||||||
| EDGE-14027 | Development | 7/29/22 9:11 AM | 7/18/22 10:23 AM | 10.95 | Ready for Code Review | 7/29/22 9:12 AM | 0.00 | ||||||||||||
| EDGE-14028 | |||||||||||||||||||
| EDGE-14029 | Development | 10/4/22 8:35 AM | 7/18/22 11:13 AM | 77.89 | Ready for Code Review | 10/4/22 8:35 AM | 0.00 | ||||||||||||
| EDGE-14035 | Development | 7/28/22 11:51 AM | 7/18/22 11:39 AM | 10.01 | Ready for Code Review | 7/28/22 11:51 AM | 0.00 | ||||||||||||
| EDGE-14038 | Development | 7/26/22 1:32 PM | 7/18/22 2:04 PM | 7.98 | Ready for Code Review | 7/26/22 1:32 PM | 0.00 | ||||||||||||
| EDGE-14039 | Development | 11/8/22 2:29 PM | 11/8/22 2:29 PM | 0.00 | Ready for Code Review | 11/8/22 4:08 PM | 0.07 | ||||||||||||
| EDGE-14041 | Development | 9/27/22 8:50 AM | 9/27/22 8:50 AM | 0.00 | Ready for Code Review | 10/3/22 1:38 PM | 6.20 | ||||||||||||
| EDGE-14042 | Development | 10/24/22 4:00 PM | 10/24/22 3:59 PM | 0.00 | Ready for Code Review | 10/28/22 12:58 PM | 3.87 | ||||||||||||
| EDGE-14043 | Development | 7/21/22 2:52 PM | 7/21/22 2:52 PM | 0.00 | Ready for Code Review | 7/21/22 2:52 PM | 0.00 | ||||||||||||
| EDGE-14054 | Development | 9/28/22 9:19 AM | 9/28/22 9:19 AM | 0.00 | Ready for Code Review | 10/3/22 12:33 PM | 5.13 | ||||||||||||
| EDGE-14064 | Development | 7/22/22 9:40 AM | 7/22/22 9:40 AM | 0.00 | Ready for Code Review | 8/2/22 9:57 AM | 11.01 | ||||||||||||
| EDGE-14065 | Development | 11/14/22 8:02 AM | 11/14/22 8:02 AM | 0.00 | Ready for Code Review | 11/18/22 7:24 AM | 3.97 | ||||||||||||
| EDGE-14074 | Development | 9/21/22 3:08 PM | 9/21/22 3:07 PM | 0.00 | |||||||||||||||
| EDGE-14139 | Development | 9/12/22 1:54 PM | 9/12/22 1:54 PM | 0.00 | Ready for Code Review | 10/10/22 9:09 AM | 27.80 | ||||||||||||
| EDGE-14146 | Development | 8/2/22 9:33 AM | 8/2/22 9:33 AM | 0.00 | Ready for Code Review | 8/3/22 1:53 PM | 1.18 | ||||||||||||
| EDGE-14173 | Development | 8/15/22 2:21 PM | 8/12/22 9:38 AM | 3.20 | Ready for Code Review | 8/15/22 2:21 PM | 0.00 | ||||||||||||
| EDGE-14180 | Development | 8/4/22 9:21 AM | 8/3/22 9:12 AM | 1.01 | Ready for Code Review | 8/17/22 12:48 PM | 13.14 | ||||||||||||
| EDGE-14181 | Development | 8/3/22 8:14 AM | 8/3/22 8:13 AM | 0.00 | Ready for Code Review | 8/9/22 3:06 PM | 6.29 | ||||||||||||
| EDGE-14188 | Development | 8/4/22 9:48 AM | 8/4/22 9:43 AM | 0.00 | Ready for Code Review | 8/9/22 4:14 PM | 5.27 | ||||||||||||
| EDGE-14199 | Development | 8/30/22 8:58 AM | 8/10/22 9:54 AM | 19.96 | Ready for Code Review | 9/1/22 4:19 PM | 2.31 | ||||||||||||
| EDGE-14215 | Development | 8/30/22 8:58 AM | 8/8/22 9:12 AM | 21.99 | Ready for Code Review | 9/12/22 8:44 AM | 12.99 | ||||||||||||
| EDGE-14222 | Development | 8/9/22 3:08 PM | 8/18/22 8:21 AM | -8.72 | Ready for Code Review | 8/18/22 8:21 AM | 8.72 | ||||||||||||
| EDGE-14239 | Development | 8/25/22 8:47 AM | 8/15/22 2:20 PM | 9.77 | Ready for Code Review | 8/25/22 8:47 AM | 0.00 | ||||||||||||
| EDGE-14240 | Development | 10/3/22 1:16 PM | 10/3/22 1:16 PM | 0.00 | Ready for Code Review | 10/7/22 2:59 PM | 4.07 | ||||||||||||
| EDGE-14241 | Development | 8/25/22 8:50 AM | 8/25/22 8:50 AM | 0.00 | Ready for Code Review | 8/25/22 8:50 AM | 0.00 | ||||||||||||
| EDGE-14242 | Development | 9/19/22 9:28 AM | 9/7/22 3:15 PM | 11.76 | |||||||||||||||
| EDGE-14259 | Development | 8/22/22 3:45 PM | 8/22/22 3:45 PM | 0.00 | Ready for Code Review | 9/8/22 9:12 AM | 16.73 | ||||||||||||
| EDGE-14260 | Development | 8/23/22 8:05 AM | 8/23/22 8:04 AM | 0.00 | Ready for Code Review | 9/1/22 10:19 AM | 9.09 | ||||||||||||
| EDGE-14271 | Development | 8/25/22 9:26 AM | 8/26/22 9:31 AM | -1.00 | Ready for Code Review | 8/31/22 9:16 AM | 5.99 | ||||||||||||
| EDGE-14274 | Development | 8/30/22 8:59 AM | 8/29/22 1:28 PM | 0.81 | Ready for Code Review | 9/1/22 8:00 AM | 1.96 | ||||||||||||
| EDGE-14286 | Development | 9/21/22 8:51 AM | 9/21/22 8:51 AM | 0.00 | Ready for Code Review | 9/26/22 8:50 AM | 5.00 | ||||||||||||
| EDGE-14289 | Development | 9/1/22 1:18 PM | 9/1/22 10:20 AM | 0.12 | Ready for Code Review | 9/13/22 9:33 AM | 11.84 | ||||||||||||
| EDGE-14295 | Development | 9/1/22 8:07 AM | 9/1/22 4:33 PM | -0.35 | Ready for Code Review | 9/1/22 4:33 PM | 0.35 | ||||||||||||
| EDGE-14306 | Development | 9/6/22 1:57 PM | 9/6/22 1:57 PM | 0.00 | Ready for Code Review | 9/7/22 12:19 PM | 0.93 | ||||||||||||
| EDGE-14310 | Development | 9/7/22 8:59 AM | 9/7/22 9:01 AM | 0.00 | Ready for Code Review | 9/13/22 4:42 PM | 6.32 | ||||||||||||
| EDGE-14311 | Development | 9/7/22 1:44 PM | 9/7/22 1:44 PM | 0.00 | Ready for Code Review | 9/7/22 4:53 PM | 0.13 | ||||||||||||
| EDGE-14318 | Development | 9/12/22 1:34 PM | 9/12/22 11:22 AM | 0.09 | Ready for Code Review | 9/12/22 4:21 PM | 0.12 | ||||||||||||
| EDGE-14319 | Development | 9/20/22 7:21 AM | 9/20/22 7:21 AM | 0.00 | Ready for Code Review | 9/22/22 10:03 AM | 2.11 | ||||||||||||
| EDGE-14336 | Development | 9/12/22 2:00 PM | 9/12/22 2:00 PM | 0.00 | Ready for Code Review | 11/1/22 9:22 AM | 49.81 | ||||||||||||
| EDGE-14349 | Development | 9/15/22 7:14 AM | 9/15/22 7:13 AM | 0.00 | Ready for Code Review | 9/27/22 5:27 PM | 12.43 | ||||||||||||
| EDGE-14352 | Development | 9/22/22 2:45 PM | 9/22/22 2:46 PM | 0.00 | Ready for Code Review | 10/5/22 12:25 PM | 12.90 | ||||||||||||
| EDGE-14366 | Development | 9/22/22 2:04 PM | 9/22/22 2:04 PM | 0.00 | Ready for Code Review | 10/5/22 8:12 AM | 12.76 | ||||||||||||
| EDGE-14376 | |||||||||||||||||||
| EDGE-14378 | Development | 10/7/22 3:13 PM | 10/7/22 3:13 PM | 0.00 | Ready for Code Review | 10/10/22 3:34 PM | 3.01 | ||||||||||||
| EDGE-14379 | Development | 9/22/22 2:05 PM | 9/22/22 2:04 PM | 0.00 | Ready for Code Review | 10/4/22 11:17 AM | 11.88 | ||||||||||||
| EDGE-14380 | Development | 9/27/22 10:53 AM | 9/27/22 10:54 AM | 0.00 | Ready for Code Review | 9/28/22 8:44 AM | 0.91 | ||||||||||||
| EDGE-14392 | Development | 11/11/22 9:15 AM | 11/11/22 9:15 AM | 0.00 | Ready for Code Review | 11/11/22 9:15 AM | 0.00 | ||||||||||||
| EDGE-14399 | Development | 9/28/22 9:12 AM | 9/28/22 9:12 AM | 0.00 | Ready for Code Review | 9/28/22 10:22 AM | 0.05 | ||||||||||||
| EDGE-14410 | Development | 10/10/22 5:39 PM | 10/10/22 5:39 PM | 0.00 | Ready for Code Review | 10/24/22 4:23 PM | 13.95 | ||||||||||||
| EDGE-14412 | Development | 10/19/22 2:54 PM | 10/19/22 2:54 PM | 0.00 | Ready for Code Review | 10/31/22 9:13 AM | 11.76 | ||||||||||||
| EDGE-14422 | Development | 10/13/22 2:42 PM | 10/13/22 2:42 PM | 0.00 | Ready for Code Review | 10/17/22 4:55 PM | 4.09 | ||||||||||||
| EDGE-14426 | Development | 10/11/22 9:50 AM | 10/11/22 9:50 AM | 0.00 | Ready for Code Review | 10/17/22 1:26 PM | 6.15 | ||||||||||||
| EDGE-14427 | Development | 10/6/22 10:18 AM | 10/6/22 10:18 AM | 0.00 | Ready for Code Review | 10/17/22 8:15 AM | 10.91 | ||||||||||||
| EDGE-14428 | Development | 10/17/22 8:14 AM | 10/6/22 10:19 AM | 10.91 | Ready for Code Review | 10/17/22 8:15 AM | 0.00 | ||||||||||||
| EDGE-14432 | Development | 10/4/22 1:25 PM | 10/4/22 1:25 PM | 0.00 | Ready for Code Review | 11/2/22 11:13 AM | 28.91 | ||||||||||||
| EDGE-14435 | Development | 10/13/22 2:25 PM | 10/13/22 2:26 PM | 0.00 | Ready for Code Review | 10/21/22 2:05 PM | 7.99 | ||||||||||||
| EDGE-14471 | Development | 10/12/22 2:40 PM | 10/12/22 2:42 PM | 0.00 | Ready for Code Review | 10/25/22 9:11 AM | 12.77 | ||||||||||||
| EDGE-14484 | Development | 10/13/22 3:09 PM | 10/13/22 3:08 PM | 0.00 | Ready for Code Review | 10/19/22 4:54 PM | 6.07 | ||||||||||||
| EDGE-14491 | Development | 10/19/22 3:01 PM | 10/19/22 3:01 PM | 0.00 | Ready for Code Review | 10/24/22 3:27 PM | 5.02 | ||||||||||||
| EDGE-14502 | Development | 10/25/22 9:20 AM | 10/24/22 1:26 PM | 0.83 | Ready for Code Review | 10/31/22 7:49 AM | 5.94 | ||||||||||||
| EDGE-14511 | Development | 10/24/22 9:35 AM | 10/24/22 9:10 AM | 0.02 | Ready for Code Review | 10/24/22 10:24 AM | 0.03 | ||||||||||||
| EDGE-14530 | Development | 10/31/22 8:54 AM | 10/28/22 1:11 PM | 2.82 | Ready for Code Review | 11/9/22 9:14 AM | 9.01 | ||||||||||||
| EDGE-14534 | Development | 10/31/22 9:41 AM | 10/31/22 9:41 AM | 0.00 | Ready for Code Review | 11/4/22 1:25 PM | 4.16 | ||||||||||||
| EDGE-14535 | Development | 11/1/22 10:57 AM | 11/1/22 10:57 AM | 0.00 | Ready for Code Review | 11/4/22 4:02 PM | 3.21 | ||||||||||||
| EDGE-14548 | Development | 11/2/22 8:39 AM | 11/1/22 9:33 AM | 0.96 | Ready for Code Review | 11/2/22 9:28 AM | 0.03 | ||||||||||||
| EDGE-14566 | Development | 11/11/22 8:56 AM | 11/10/22 3:12 PM | 0.74 | Ready for Code Review | 11/14/22 3:12 PM | 3.26 | ||||||||||||
| EDGE-14580 | Development | 11/9/22 10:31 AM | 11/9/22 10:31 AM | 0.00 | Ready for Code Review | 11/11/22 1:18 PM | 2.12 | ||||||||||||
| EDGE-14582 | Development | 11/18/22 2:39 PM | 11/18/22 2:39 PM | 0.00 | Ready for Code Review | 11/28/22 10:24 AM | 9.82 | ||||||||||||
| EDGE-14584 | Development | 11/15/22 1:48 PM | 11/15/22 1:02 PM | 0.03 | Ready for Code Review | 11/16/22 8:12 AM | 0.77 | ||||||||||||
| EDGE-14585 | Development | 12/21/22 1:07 PM | 12/21/22 1:07 PM | 0.00 | Ready for Code Review | 12/22/22 1:06 PM | 1.00 | ||||||||||||
| EDGE-14586 | Development | 12/15/22 9:32 AM | 12/15/22 9:32 AM | 0.00 | Ready for Code Review | 12/20/22 11:06 AM | 5.06 | ||||||||||||
| EDGE-14587 | Development | 11/17/22 8:18 AM | 11/17/22 8:19 AM | 0.00 | Ready for Code Review | 11/17/22 8:19 AM | 0.00 | ||||||||||||
| EDGE-14588 | Development | 11/17/22 7:42 AM | 11/17/22 7:42 AM | 0.00 | Ready for Code Review | 11/17/22 3:41 PM | 0.33 | ||||||||||||
| EDGE-14589 | Development | 10/4/23 3:08 PM | 10/4/23 3:08 PM | 0.00 | Ready for Code Review | 10/13/23 12:10 PM | 8.88 | ||||||||||||
| EDGE-14592 | Development | 11/15/22 10:49 AM | 11/15/22 10:49 AM | 0.00 | Ready for Code Review | 11/17/22 9:46 AM | 1.96 | ||||||||||||
| EDGE-14596 | Development | 1/13/23 12:01 PM | 1/13/23 12:01 PM | 0.00 | Ready for Code Review | 1/17/23 4:13 PM | 4.17 | ||||||||||||
| EDGE-14598 | Development | 11/28/22 10:49 AM | 11/28/22 10:49 AM | 0.00 | Ready for Code Review | 11/30/22 9:21 AM | 1.94 | ||||||||||||
| EDGE-14601 | Development | 11/9/22 9:14 AM | 11/1/22 8:54 AM | 8.01 | Ready for Code Review | 11/30/22 12:41 PM | 21.14 | ||||||||||||
| EDGE-14602 | Development | 11/14/22 9:08 AM | 11/1/22 8:55 AM | 13.01 | Ready for Code Review | 12/15/22 11:53 AM | 31.11 | ||||||||||||
| EDGE-14603 | Development | 12/15/22 9:16 AM | 11/1/22 8:55 AM | 44.01 | Ready for Code Review | 1/3/23 8:57 AM | 18.99 | ||||||||||||
| EDGE-14615 | Development | 11/3/22 11:02 AM | 11/3/22 11:01 AM | 0.00 | Ready for Code Review | 11/3/22 4:42 PM | 0.24 | ||||||||||||
| EDGE-14618 | Development | 11/8/22 4:58 PM | 11/8/22 4:58 PM | 0.00 | Ready for Code Review | 11/10/22 3:12 PM | 1.93 | ||||||||||||
| EDGE-14622 | Development | 11/2/22 4:38 PM | 11/2/22 2:26 PM | 0.09 | Ready for Code Review | 11/2/22 5:57 PM | 0.06 | ||||||||||||
| EDGE-14623 | Development | 11/4/22 9:57 AM | 11/4/22 9:57 AM | 0.00 | Ready for Code Review | 11/7/22 3:06 PM | 3.21 | ||||||||||||
| EDGE-14629 | Development | 11/7/22 11:10 AM | 11/7/22 11:11 AM | 0.00 | Ready for Code Review | 11/7/22 4:13 PM | 0.21 | ||||||||||||
| EDGE-14639 | Development | 11/7/22 9:38 AM | 11/7/22 9:38 AM | 0.00 | Ready for Code Review | 11/7/22 2:10 PM | 0.19 | ||||||||||||
| EDGE-14643 | Development | 11/8/22 8:03 AM | 11/8/22 7:49 AM | 0.01 | Ready for Code Review | 11/8/22 8:03 AM | 0.00 | ||||||||||||
| EDGE-14650 | Development | 12/5/22 4:16 PM | 12/5/22 4:16 PM | 0.00 | Ready for Code Review | 12/12/22 2:01 PM | 6.91 | ||||||||||||
| EDGE-14651 | Development | 11/17/22 4:14 PM | 11/17/22 4:14 PM | 0.00 | Ready for Code Review | 11/22/22 4:01 PM | 4.99 | ||||||||||||
| EDGE-14664 | Development | 11/14/22 9:15 AM | 11/14/22 9:15 AM | 0.00 | Ready for Code Review | 11/15/22 3:47 PM | 1.27 | ||||||||||||
| EDGE-14666 | |||||||||||||||||||
| EDGE-14670 | Development | 12/7/22 3:41 PM | 11/30/22 2:26 PM | 7.05 | Ready for Code Review | 12/18/22 10:50 PM | 11.30 | ||||||||||||
| EDGE-14674 | Development | 11/16/22 1:05 PM | 11/16/22 1:05 PM | 0.00 | Ready for Code Review | 11/18/22 12:44 PM | 1.99 | ||||||||||||
| EDGE-14681 | Development | 2/2/23 11:14 AM | 2/2/23 11:14 AM | 0.00 | Ready for Code Review | 2/23/23 2:53 PM | 21.15 | ||||||||||||
| EDGE-14682 | Development | 2/27/23 2:45 PM | 2/27/23 2:45 PM | 0.00 | Ready for Code Review | 3/1/23 3:01 PM | 2.01 | ||||||||||||
| EDGE-14684 | Development | 12/1/22 9:33 AM | 12/1/22 9:33 AM | 0.00 | Ready for Code Review | 12/7/22 11:33 AM | 6.08 | ||||||||||||
| EDGE-14685 | Development | 12/6/22 10:48 AM | 12/6/22 10:48 AM | 0.00 | Ready for Code Review | 12/14/22 3:20 PM | 8.19 | ||||||||||||
| EDGE-14691 | Development | 11/14/22 3:15 PM | 11/14/22 3:15 PM | 0.00 | Ready for Code Review | 11/16/22 12:56 PM | 1.90 | ||||||||||||
| EDGE-14701 | Development | 11/15/22 3:50 PM | 11/15/22 3:50 PM | 0.00 | Ready for Code Review | 11/16/22 11:58 AM | 0.84 | ||||||||||||
| EDGE-14702 | Development | 11/15/22 1:48 PM | 11/30/22 7:18 AM | -14.73 | Ready for Code Review | 12/5/22 7:22 AM | 19.73 | ||||||||||||
| EDGE-14703 | Development | 11/15/22 11:10 AM | 11/15/22 11:10 AM | 0.00 | Ready for Code Review | 11/15/22 11:10 AM | 0.00 | ||||||||||||
| EDGE-14704 | Development | 11/15/22 2:04 PM | 11/15/22 10:17 AM | 0.16 | Ready for Code Review | 11/16/22 2:24 PM | 1.01 | ||||||||||||
| EDGE-14707 | Development | 11/29/22 1:57 PM | 11/29/22 1:57 PM | 0.00 | Ready for Code Review | 12/5/22 1:21 PM | 5.98 | ||||||||||||
| EDGE-14708 | Development | 11/17/22 3:42 PM | 11/17/22 3:42 PM | 0.00 | Ready for Code Review | 11/29/22 2:54 PM | 11.97 | ||||||||||||
| EDGE-14710 | |||||||||||||||||||
| EDGE-14716 | Development | 12/9/22 9:39 AM | 12/9/22 9:39 AM | 0.00 | Ready for Code Review | 12/15/22 12:51 PM | 6.13 | ||||||||||||
| EDGE-14718 | Development | 4/20/23 11:09 AM | 4/6/23 11:18 AM | 13.99 | Ready for Code Review | 4/23/23 7:56 PM | 3.37 | ||||||||||||
| EDGE-14721 | Development | 11/28/22 8:26 AM | 11/28/22 8:26 AM | 0.00 | Ready for Code Review | 12/6/22 8:58 AM | 8.02 | ||||||||||||
| EDGE-14785 | Development | 12/6/22 1:57 PM | 12/6/22 1:56 PM | 0.00 | Ready for Code Review | 12/8/22 4:20 PM | 2.10 | ||||||||||||
| EDGE-14789 | Development | 12/7/22 9:24 AM | 12/7/22 9:24 AM | 0.00 | Ready for Code Review | 12/9/22 8:05 AM | 1.94 | ||||||||||||
| EDGE-14804 | Development | 12/14/22 1:32 PM | 12/14/22 1:32 PM | 0.00 | Ready for Code Review | 12/20/22 10:51 AM | 5.89 | ||||||||||||
| EDGE-14805 | Development | 12/16/22 7:20 AM | 12/15/22 2:41 PM | 0.69 | Ready for Code Review | 12/23/22 9:27 AM | 7.09 | ||||||||||||
| EDGE-14806 | Development | 12/22/22 7:38 AM | 12/22/22 7:38 AM | 0.00 | Ready for Code Review | 1/3/23 11:09 AM | 12.15 | ||||||||||||
| EDGE-14807 | Development | 1/5/23 1:37 PM | 1/5/23 1:37 PM | 0.00 | Ready for Code Review | 1/12/23 2:39 PM | 7.04 | ||||||||||||
| EDGE-14808 | Development | 1/6/23 9:33 AM | 1/6/23 9:33 AM | 0.00 | Ready for Code Review | 1/12/23 2:39 PM | 6.21 | ||||||||||||
| EDGE-14811 | Development | 12/16/22 7:26 AM | 12/16/22 7:26 AM | 0.00 | Ready for Code Review | 12/20/22 2:08 PM | 4.28 | ||||||||||||
| EDGE-14812 | Development | 12/21/22 4:00 PM | 12/21/22 4:00 PM | 0.00 | Ready for Code Review | 12/23/22 11:11 AM | 1.80 | ||||||||||||
| EDGE-14814 | Development | 12/15/22 4:19 PM | 12/15/22 4:19 PM | 0.00 | Ready for Code Review | 12/16/22 2:56 PM | 0.94 | ||||||||||||
| EDGE-14815 | Development | 12/20/22 9:27 AM | 12/20/22 5:30 PM | -0.34 | Ready for Code Review | 12/21/22 11:55 AM | 1.10 | ||||||||||||
| EDGE-14821 | Development | 12/23/22 9:27 AM | 12/23/22 9:27 AM | 0.00 | Ready for Code Review | 12/28/22 7:51 AM | 4.93 | ||||||||||||
| EDGE-14822 | Development | 1/3/23 11:19 AM | 1/3/23 11:18 AM | 0.00 | Ready for Code Review | 1/11/23 3:05 PM | 8.16 | ||||||||||||
| EDGE-14823 | Development | 12/15/22 2:30 PM | 12/15/22 2:30 PM | 0.00 | Ready for Code Review | 12/16/22 10:58 AM | 0.85 | ||||||||||||
| EDGE-14837 | Development | 1/3/23 10:49 AM | 1/3/23 10:49 AM | 0.00 | Ready for Code Review | 1/4/23 9:53 AM | 0.96 | ||||||||||||
| EDGE-14839 | Development | 1/4/23 11:22 AM | 1/4/23 11:22 AM | 0.00 | Ready for Code Review | 1/5/23 3:09 PM | 1.16 | ||||||||||||
| EDGE-14841 | Development | 1/3/23 1:08 PM | 1/3/23 2:23 PM | -0.05 | Ready for Code Review | 1/5/23 4:25 PM | 2.14 | ||||||||||||
| EDGE-14844 | Development | 2/9/23 1:34 PM | 2/9/23 1:02 PM | 0.02 | Ready for Code Review | 2/13/23 6:12 PM | 4.19 | ||||||||||||
| EDGE-14850 | Development | 7/18/23 3:10 PM | 7/18/23 3:10 PM | 0.00 | Ready for Code Review | 7/20/23 4:38 PM | 2.06 | ||||||||||||
| EDGE-14854 | Development | 1/3/23 6:54 PM | 1/3/23 6:53 PM | 0.00 | Ready for Code Review | 2/22/23 4:14 PM | 49.89 | ||||||||||||
| EDGE-14859 | Development | 1/5/23 1:38 PM | 1/12/23 9:20 AM | -6.82 | Ready for Code Review | 1/12/23 9:20 AM | 6.82 | ||||||||||||
| EDGE-14861 | Development | 1/18/23 9:41 AM | 1/18/23 9:41 AM | 0.00 | Ready for Code Review | 1/20/23 4:07 PM | 2.27 | ||||||||||||
| EDGE-14871 | Development | 1/12/23 9:25 AM | 1/12/23 9:25 AM | 0.00 | Ready for Code Review | 1/16/23 10:07 AM | 4.03 | ||||||||||||
| EDGE-14872 | Development | 1/12/23 9:09 AM | 1/12/23 9:09 AM | 0.00 | Ready for Code Review | 1/13/23 3:08 PM | 1.25 | ||||||||||||
| EDGE-14874 | Development | 1/18/23 9:33 AM | 1/18/23 9:33 AM | 0.00 | Ready for Code Review | 1/25/23 2:37 PM | 7.21 | ||||||||||||
| EDGE-14883 | Development | 1/18/23 9:27 AM | 1/18/23 9:39 AM | -0.01 | Ready for Code Review | 1/26/23 12:00 PM | 8.11 | ||||||||||||
| EDGE-14889 | Development | 1/10/23 10:39 AM | 1/10/23 10:39 AM | 0.00 | Ready for Code Review | 1/10/23 3:33 PM | 0.20 | ||||||||||||
| EDGE-14902 | Development | 1/16/23 10:05 AM | 1/16/23 9:47 AM | 0.01 | Ready for Code Review | 1/24/23 7:31 AM | 7.89 |
Basically I want to enhance the calculation formula in Column AQ to subtract whatever time blocked in my 1st table when the ticket numbers match. Right now the time in a particular workflow status is just a simple subtract of the date/time in Column AQ from the date/time in Column AK. I want to factor in subtracting that time(s) blocked to my time in that Development workflow status as well.
Some examples, for row 37 of the 2nd table for ticket # EDGE-14199, my result before accounting for blocked time is 2.31 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14199 being blocked, the 1st record of it being blocked would NOT be subtracted because the date/time start of the block did not start before the date/time start of the workflow status of Development. The 2nd record if it being blocked would also NOT be subtracted because of the date/time start of the block did not start before the date/time start of the workflow status of Development. So my result would remain 2.31 in Column AQ.
For row 41 of the 2nd table for ticket # EDGE-14240, my result before accounting for blocked time is 4.07 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 1 record of EDGE-14199 being blocked, the record of it being blocked would NOT be subtracted because the date/time start of the block did not start before the date/time start of the workflow status of Development. So my result in Column AQ would remain 4.07.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 56 of the 2nd table for ticket # EDGE-14336, my result before accounting for blocked time is 49.81 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, BOTH records of it being blocked WOULD be subtracted because both date/time starts of the blocks did start before the date/time start of the workflow status of Development. So after subtracting my blocked times of 9.01 and 3.92, my result in Column AQ would be about 36.88 rather than 49.81.
For row 58 of the 2nd table for ticket # EDGE-14352, my result before accounting for blocked time is 12.90 in Column AQ. I’d like to index, sum, and subtract the time blocked from my 1st table for that ticket. There’s 2 records of EDGE-14336 being blocked, ONLY ONE of the records of it being blocked WOULD be subtracted because date/time start of one of the block records did start before the date/time start of the workflow status of Development (the 1st record of it being blocked). That’s because the start time of the 1st block record (9/26 at 4:22PM) is after the date/time start of the Workflow status in Column N (9/22 at 2:45PM), and before the date/time end in Column S (10/5 12:25PM). The record of the ticket’s 2nd time blocked, the date/time start (10/5 3:51 PM) is after the date/time end in Column S (10/5 12:25PM). So after subtracting my blocked times of 3.71, my result in Column AQ would be about 9.19 rather than 12.90.
I’d also like to ensure there are no negative results, so if after the subtraction (if applicable) of time blocked, if the result is negative, then just want the result defaulted to zero.
Appreciate any help factoring in this indexing and subtracting of time blocked into my time spent calculation in the Development workflow status.
