Calculating the time elapsed between events that vary based on different cell criteria.

[mhighland]

I'm working with the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 827"]
<tbody>[TR]
[TD]EVENTID[/TD]
[TD]PAGE[/TD]
[TD]STATUS[/TD]
[TD]TYPE[/TD]
[TD]JOBID[/TD]
[TD]TIMESTAMP[/TD]
[TD]Time Elapsed: Days[/TD]
[TD]Time Elapsed: Hours[/TD]
[/TR]
[TR]
[TD="align: right"]49239521[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Wednesday, June 28, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]49239522[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Wednesday, July 5, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50682764[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Thursday, September 21, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50682775[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Thursday, September 21, 2017[/TD]
[TD="align: right"]03:10:29[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]Reviewer[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Monday, September 25, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD="align: right"]03:10:29:13[/TD]
[/TR]
[TR]
[TD="align: right"]50725746[/TD]
[TD]Reviewer[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Monday, September 25, 2017[/TD]
[TD="align: right"]00:22:15[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50746671[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Tuesday, September 26, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50752728[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]Tuesday, September 26, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]49267081[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Wednesday, June 28, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]49267082[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Friday, July 7, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50335478[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Monday, September 4, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50446072[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Saturday, September 9, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50446074[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Saturday, September 9, 2017[/TD]
[TD="align: right"]02:02:47[/TD]
[TD]UNDEFINED[/TD]
[/TR]
[TR]
[TD="align: right"]50471615[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Monday, September 11, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD="align: right"]02:02:47:54[/TD]
[/TR]
[TR]
[TD="align: right"]50473794[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]Monday, September 11, 2017[/TD]
[TD]UNDEFINED[/TD]
[TD]UNDEFINED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The TIMESTAMP column was in the format of dd:mm:yy hh:mm (there are several spaces between the date and the time) but I converted it to the Long Date format so I could calculate the difference between two dates based on specific criteria. I did not think it would work otherwise.

Initially, I wanted to know the amount of time elapsed between each time a "Shopper" (Column "Page") placed a report in "Completed (Column "Status") and a "Reviewer" (Column "Page") changed the status of the report. Since it was always the "Reviewer" that made the next action, the "Reviewer" is always in the row immediately after the row where the "Shopper" put a report in "Completed." Thus, I was able to use the following formula to correctly calculate the amount of time elapsed between the two events:

=IF(ISERROR(IF(AND($B5="Shopper", $C5="Completed"),$I6-$I5,IF(AND($B5="", $C5=""),$I6-$I5,"UNDEFINED"))),"",(IF(AND($B5="Shopper", $C5="Completed"),$I6-$I5,IF(AND($B5="", $C5=""),$I6-$I5,"UNDEFINED"))))

(Note: the cell references, ($B5="Shopper", $C5="Completed"), do not necessarily match up with the table excerpt above; they are merely illustrative.)

The result/output of the formula is in the "Time Elapsed: Hours" Column. However, the date stamp is modified to show days, hours, minutes, seconds; (dd:hh:mm:ss), so it is not actually the number of hours that have elapsed, it the the number of days, hours, minutes, and seconds that have elapsed.

My question is whether there is a way to modify the formula above so that it retrieves the time elapsed between when a "Shopper" puts a report in "Completed" and when the "Reviewer" completes any action whatsoever? For example, in the chart below, would it be possible to have a formula that calculates the time elapsed between the first event (50682775) and the second event (50722162) as well as between the first event (50682775) and the third event (50725746)?

[TABLE="class: grid, width: 827"]
<tbody>[TR]
[TD="align: right"]50682775[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD="align: right"]50722162[/TD]
[TD]Reviewer[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD="align: right"]50725746[/TD]
[TD]Reviewer[/TD]
[TD]Completed[/TD]
[/TR]
</tbody>[/TABLE]

There are seven different actions that the "Reviewer" can take.

Ideally, the formula could be copied down the entire sheet.

I can provide clarification where needed, but I hope this is enough to get us started.

I appreciate any help that you can provide.

 

[mhighland]

How's this...

[TABLE="width: 1219"]
<tbody>[TR]
[TD]EVENTID[/TD]
[TD]PAGE[/TD]
[TD]STATUS[/TD]
[TD]TYPE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JOBID[/TD]
[TD]TIMESTAMP[/TD]
[TD]Time Elapsed: Days[/TD]
[TD]Time Elapsed: Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49239521[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]28/06/17 06:56:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49239522[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]05/07/17 21:42:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50682764[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]21/09/17 19:21:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50682775[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]21/09/17 19:22:00[/TD]
[TD="align: right"]03:10:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50722162[/TD]
[TD]Reviewer[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]25/09/17 05:52:00[/TD]
[TD][/TD]
[TD="align: right"]03:10:30:00[/TD]
[/TR]
[TR]
[TD]50725746[/TD]
[TD]Reviewer[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]25/09/17 09:19:00[/TD]
[TD][/TD]
[TD="align: right"]03:13:57:00[/TD]
[/TR]
[TR]
[TD]50746671[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]26/09/17 07:34:00[/TD]
[TD][/TD]
[TD="align: right"]04:12:12:00[/TD]
[/TR]
[TR]
[TD]50752728[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6622643[/TD]
[TD]26/09/17 12:19:00[/TD]
[TD][/TD]
[TD="align: right"]04:16:57:00[/TD]
[/TR]
[TR]
[TD]49267081[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]28/06/17 06:56:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49267082[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]07/07/17 06:48:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50335478[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]09/04/17 09:25:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50446072[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]09/09/17 00:13:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50446074[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]09/09/17 00:14:00[/TD]
[TD="align: right"]01:02:47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50471615[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]09/11/17 03:01:00[/TD]
[TD][/TD]
[TD="align: right"]01:02:47:00[/TD]
[/TR]
[TR]
[TD]50473794[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6630464[/TD]
[TD]09/11/17 06:12:00[/TD]
[TD][/TD]
[TD="align: right"]01:05:58:00[/TD]
[/TR]
</tbody>[/TABLE]


The downside of these formulae is that you will need a blank row below your header and do not drag the formula into the blank row. So row 3 will contain your first row of data.

Put this into F3 and drag down.

=IF(AND(B3="Shopper", C3="Completed"),I4-I3,IF(AND(B3="", C3=""),I4-I3,""))

Put this into G3 and drag down

=IF(H3<>H2,"",IF(J2< >"",J2,IF(K2<>"",K2+I3-I2,"")))

You may notice I have changed the UNDEFINED to be blank as I thought it looked neater and was quicker to type but feel free to change it back.

OK, I understand the first formula and was able to successfully apply to my workbook. However, the second equation prompts an error message says there is a problem with the formula and suggests it be corrected to "=IF(H3<>H2),"",IF(J2<"",J2,IF(K2<>"",K2+I3-I2,")))

However, I think this might be because our column letters/references might not be aligned.

So long as i have a working formula, I can identify the cells each element refers to. Thus, I can reverse engineer the formula to fit my original worksheet.

So to simplify and make sure we're on the same page, let's work from this table with Column letters "A" through "H," as in the table below.

[TABLE="class: grid, width: 800"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]EVENTID[/TD]
[TD]PAGE[/TD]
[TD]STATUS[/TD]
[TD]TYPE[/TD]
[TD]JOBID[/TD]
[TD]TIMESTAMP[/TD]
[TD]TIME ELAPSED 1[/TD]
[TD]TIME ELAPSED 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49239521[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]6/28/17 06:56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49239522[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]7/5/17 21:42[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50682764[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/21/17 19:21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50682775[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/21/17 19:22[/TD]
[TD="align: right"]3.436956019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50722162[/TD]
[TD]Reviewer[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/25/17 05:52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50725746[/TD]
[TD]Reviewer[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/25/17 09:19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50746671[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/26/17 07:34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50752728[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/26/17 12:19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49267081[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]6/28/17 06:56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49267082[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]7/7/17 06:48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50335478[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/4/17 09:25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50446072[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/9/17 00:13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50446074[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/9/17 00:14[/TD]
[TD="align: right"]2.116597222[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50471615[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/11/17 03:01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50473794[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/11/17 06:12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first "row" in the table above is not supposed to be a row—I just wanted to clarify the correct Column references.

I added a blank row between rows "1" and "2" so that the data now start in row "3". I copied your first formula in cell "G3", instead of "F3", as instructed, and copied down the page. "=IF(AND(B3="Shopper", C3="Completed"),I4-I3,IF(AND(B3="", C3=""),I4-I3,""))"

The results I got are in Column "G".

Obviously your second equation will not work with the table above as it is currently written, so that is what I want to clarify.

I assume that, in its current configuration, the second equation will go in Column "H," correct? If so, how should it be modified to function as it should? I see in the table you pasted above that it appears to be working correctly, I'm just having trouble duplicating.

Thank you for your help with this, I think we're almost there.

 

[C with no eyes]

I'm sorry. I initially seem to have told you to put the formula into the wrongs cells. I was supposed to say drag down from J3 and K3! I'm going to blame it on the time of day!

Using your original layout where jobid is H, the formulae literally translate as follows:

Elapsed days J3=IF(AND(B3="Shopper", C3="Completed"),I4-I3,IF(AND(B3="", C3=""),I4-I3,""))

=IF(AND(B3="Shopper", C3="Completed"),
If B on this row is shopper and C on this row is completed

I4-I3,
then find the difference between the timestamp on the row below and this row's timestamp.

IF(AND(B3="", C3=""),I4-I3,""))
If this row's B and C are blank then also find the difference between the timestamp on the row below and this row's timestamp.

(To be honest, I'm not even sure you need this as J3=IF(AND(B3="Shopper", C3="Completed"),I4-I3,"") does the same thing but then I don't have access to all of your data. In fact I'm not even sure of the necessity of this column, to me, it seems redundant but it's your prerogative. If you agree then go straight to the bottom of this post.)

Elapsed hours K3=IF(H3 < > H2,"",IF(J2 < > "",J2,IF(K2 < > "",K2+I3-I2,"")))

=IF(H3 < > H2,
If this row's jobid is different to the jobid above

"",
then be a blank cell.

IF(J2 < > "",
If the elapsed days cell on the row above is not blank

J2,
then show the same value as elapsed days from the row above.

IF(K2 < > "",
If the elapsed hours row above is not blank

K2+I3-I2,
then add to elapsed hours on the row above to the difference between timestamp of this row and the above row.

"")))
Otherwise, be blank.

As mentioned further up I have another suggestion...

Delete column J Elapsed days. Elapsed hours is now column J.

J3

=IF(H3<>H2,"",IF(AND(B2="shopper",C2="completed"),I3-I2,IF(J2<>"",J2+I3-I2,"")))

Sorry about all the spaces.

 

[C with no eyes]

If you don't want there to be a time showing when it's been locked then add IF(C3="locked","", at the beginning and a closing bracket at the very end.

i.e.

[COLOR=#333333]=IF(C3="locked","",IF(H3<>H2,"",IF(AND(B2="shopper",C2="completed"),I3-I2,IF(J2<>"",J2+I3-I2,""))))[/COLOR]

I'm sure you know how to format the cells but if not:
Select the entire row J. Right click | format cells | custom | Type: dd:hh:mm:ss | OK

 

[mhighland]

If you don't want there to be a time showing when it's been locked then add IF(C3="locked","", at the beginning and a closing bracket at the very end.

i.e.

[COLOR=#333333]=IF(C3="locked","",IF(H3<>H2,"",IF(AND(B2="shopper",C2="completed"),I3-I2,IF(J2<>"",J2+I3-I2,""))))[/COLOR]

I'm sure you know how to format the cells but if not:
Select the entire row J. Right click | format cells | custom | Type: dd:hh:mm:ss | OK

Awesome! This last formula works exactly how I need. Everything you mentioned in the previous post made perfect sense, and I agreed that it was best to delete Column "J." I also am really happy that you provided an option to not calculate values for reports in "Locked" status—it is distracting and isn't needed. I was toying around with the syntax of the formula trying to add in this conditional, but I never thought to put it at the beginning. I tried embedding it in the second, and then the third, "IF" statements to no avail.

Anyway, here is the result I got, along with some conditional formatting I added to highlight the any time that is more than 32 hours past the time a shopper put a report in "Completed" status.

I've redacted all the sensitive information in the worksheet:

[TABLE="class: grid, width: 1587"]
<tbody>[TR]
[TD]EVENTID
[/TD]
[TD]PAGE[/TD]
[TD]STATUS[/TD]
[TD]TYPE[/TD]
[TD]WHOID[/TD]
[TD]FIRSTNAME[/TD]
[TD]LASTNAME
[/TD]
[TD]JOBID[/TD]
[TD]TIMESTAMP[/TD]
[TD]CLIENTID[/TD]
[TD]CLIENTNAME[/TD]
[TD]SURVEYID[/TD]
[TD]SURVEYNAME[/TD]
[TD]LOCCITY[/TD]
[TD]COUNTRYNAME[/TD]
[TD]AREACODE[/TD]
[TD]TIME ELAPSED[/TD]
[/TR]
[TR]
[TD="align: right"]49239521[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]176754[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]6/28/17 06:56[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49239522[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]176754[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]7/5/17 21:42[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50682764[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]891461[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/21/17 19:21[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50682775[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]891461[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/21/17 19:22[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50722162[/TD]
[TD]Reviewer[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]189238[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/25/17 05:52[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.436956019
[/TD]
[/TR]
[TR]
[TD="align: right"]50725746[/TD]
[TD]Reviewer[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]176754[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/25/17 09:19[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.580729167[/TD]
[/TR]
[TR]
[TD="align: right"]50746671[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]189238[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/26/17 07:34[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.507962963[/TD]
[/TR]
[TR]
[TD="align: right"]50752728[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]109067[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6622643[/TD]
[TD="align: right"]9/26/17 12:19[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49267081[/TD]
[TD]Scheduler[/TD]
[TD]Created[/TD]
[TD]Private[/TD]
[TD="align: right"]190502[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]6/28/17 06:56[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49267082[/TD]
[TD]Scheduler[/TD]
[TD]New[/TD]
[TD]Assigned[/TD]
[TD="align: right"]190502[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]7/7/17 06:48[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50335478[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]394176[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/4/17 09:25[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50446072[/TD]
[TD]Shopper[/TD]
[TD]Incomplete[/TD]
[TD]Submitted[/TD]
[TD="align: right"]394176[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/9/17 00:13[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50446074[/TD]
[TD]Shopper[/TD]
[TD]Completed[/TD]
[TD]Submitted[/TD]
[TD="align: right"]394176[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/9/17 00:14[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50471615[/TD]
[TD]Reviewer[/TD]
[TD]Finalized[/TD]
[TD]Submitted[/TD]
[TD="align: right"]173554[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/11/17 03:01[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.116597222
[/TD]
[/TR]
[TR]
[TD="align: right"]50473794[/TD]
[TD][/TD]
[TD]Locked[/TD]
[TD][/TD]
[TD="align: right"]109067[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6630464[/TD]
[TD="align: right"]9/11/17 06:12[/TD]
[TD="align: right"]2725[/TD]
[TD][/TD]
[TD="align: right"]17982[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And here's my final formula: =IF(C2="Locked","",IF(H2<>H1,"",IF(AND(B1="Shopper",C1="Completed"),I2-I1,IF(Q1<>"",Q1+I2-I1,""))))

Fortunately, there isn't a need for a blank row between the headings and the start of the data, so I've been able to turn this into a template that others can simply copy-paste data into and have everything calculate automatically.

I do know how to format the timestamp, but thank you for taking the time to show me anyway.

All questions above answered! Thank you so, so much for all your help. I truly appreciate it.

As I continue to improve my Excel skills, I will definitely pay it forward and help others as you have helped me.

One final question? Is there anything I should do to mark this question as solved/answered? I'm unsure of the protocol here.

Many thanks!

 

[C with no eyes]

No problem at all. I love this kind of stuff (my other half is a gamer in his spare time and I've now run out of spreadsheets I can create at work while he's playing so turned to the internet to occupy my time! Sad, I know, but I'm a numbers and logic gal.)

I actually don't know how to mark a post as solved but if we stop posting it will eventually disappear into the nether