Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
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:

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 DetailsTicket started Gathering RequirementsTicket Ready to DevelopTicket started DevelopmentTickets finishing Development
Ticket NumberFix VersionStory PointsPairsSpike (Y/N)Rework after Code Review (Y/N)Ticked failed QAStatusDate/Time Status ChangedStatusDate/Time Status ChangedTime till Ready for DevelopmentStatusDate/Time Status ChangedDeveloper Name (if available)Date/Time Assignee ChangedreconStatusDate/Time Status ChangedTime till Ready for Code Review
EDGE-13283
EDGE-13293
EDGE-13901
EDGE-13904
EDGE-13907
EDGE-13908
EDGE-13909Development
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-13974Development
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-13985Development
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-14008Development
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-14009Development
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-14024Development
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-14026Development
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-14027Development
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-14029Development
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-14035Development
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-14038Development
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-14039Development
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-14041Development
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-14042Development
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-14043Development
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-14054Development
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-14064Development
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-14065Development
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-14074Development
9/21/22 3:08 PM​
9/21/22 3:07 PM​
0.00​
EDGE-14139Development
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-14146Development
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-14173Development
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-14180Development
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-14181Development
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-14188Development
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-14199Development
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-14215Development
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-14222Development
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-14239Development
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-14240Development
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-14241Development
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-14242Development
9/19/22 9:28 AM​
9/7/22 3:15 PM​
11.76​
EDGE-14259Development
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-14260Development
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-14271Development
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-14274Development
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-14286Development
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-14289Development
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-14295Development
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-14306Development
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-14310Development
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-14311Development
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-14318Development
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-14319Development
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-14336Development
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-14349Development
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-14352Development
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-14366Development
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-14378Development
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-14379Development
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-14380Development
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-14392Development
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-14399Development
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-14410Development
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-14412Development
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-14422Development
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-14426Development
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-14427Development
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-14428Development
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-14432Development
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-14435Development
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-14471Development
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-14484Development
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-14491Development
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-14502Development
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-14511Development
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-14530Development
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-14534Development
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-14535Development
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-14548Development
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-14566Development
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-14580Development
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-14582Development
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-14584Development
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-14585Development
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-14586Development
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-14587Development
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-14588Development
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-14589Development
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-14592Development
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-14596Development
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-14598Development
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-14601Development
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-14602Development
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-14603Development
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-14615Development
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-14618Development
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-14622Development
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-14623Development
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-14629Development
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-14639Development
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-14643Development
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-14650Development
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-14651Development
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-14664Development
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-14670Development
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-14674Development
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-14681Development
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-14682Development
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-14684Development
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-14685Development
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-14691Development
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-14701Development
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-14702Development
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-14703Development
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-14704Development
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-14707Development
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-14708Development
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-14716Development
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-14718Development
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-14721Development
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-14785Development
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-14789Development
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-14804Development
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-14805Development
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-14806Development
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-14807Development
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-14808Development
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-14811Development
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-14812Development
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-14814Development
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-14815Development
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-14821Development
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-14822Development
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-14823Development
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-14837Development
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-14839Development
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-14841Development
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-14844Development
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-14850Development
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-14854Development
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-14859Development
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-14861Development
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-14871Development
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-14872Development
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-14874Development
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-14883Development
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-14889Development
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-14902Development
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top