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.

 

[C with no eyes]

What is in column I?

 

[mhighland]

What is in column I?

Sorry, there was sensitive data in many of the Columns, so I hid them. I forgot that there were no Column letter headings on the table.

Column "I" is the Column called "TIMESTAMP."

The Columns called "EVENTID", "PAGE", "STATUS", and "TIME" are Columns "A" through "D", respectively, and the Columns called "JOBID" and "TIMESTAMP" are Columns "H" and "I", respectively.

I hope this helps, and sorry for the confusion!

Thank you for taking the time to reply.

 

[C with no eyes]

Would you mind putting the timestamp back to dd/mm/yy hh:mm:ss so I have some real data? I can calculate the days difference in this format but it's slow making up times!!

 

[C with no eyes]

There are a couple of things that jump out at me as I'm messing around.

Firstly, Get rid of IF(ISERROR([calculation]),"",[calculation])
Replace it with IFERROR([calculation],"")

Secondly, the difference between days is DAYS([end date],[start date])

When do you want the 'time elapsed days' to stop counting accumulatively? When the reviewer finalizes?

 

[mhighland]

Would you mind putting the timestamp back to dd/mm/yy hh:mm:ss so I have some real data? I can calculate the days difference in this format but it's slow making up times!!

Would you mind putting the timestamp back to dd/mm/yy hh:mm:ss so I have some real data? I can calculate the days difference in this format but it's slow making up times!!

Here you are. I think this this what you need, but let me know if it isn't. I'm happy to provide anything that might help.


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]EVENTID[/TD]
[TD]PAGE[/TD]
[TD]STATUS[/TD]
[TD]TYPE[/TD]
[TD]JOBID[/TD]
[TD]TIMESTAMP[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]


Again, thank you. I do appreciate your willingness to help with this.

Sorry for all the back and forth!

 

[mhighland]

There are a couple of things that jump out at me as I'm messing around.

Firstly, Get rid of IF(ISERROR([calculation]),"",[calculation])
Replace it with IFERROR([calculation],"")

Secondly, the difference between days is DAYS([end date],[start date])

When do you want the 'time elapsed days' to stop counting accumulatively? When the reviewer finalizes?

OK, so on the first count, I changed the formula to IFERROR (which I'm only just learning about now) as you said. I got this: =IFERROR((IF(AND($B6="Shopper", $C6="Completed"),$I7-$I6,IF(AND($B6="", $C6=""),$I7-$I6,"UNDEFINED"))),""), which yielded the same result as before—which is a good thing—but is far simpler, which is also a good thing.

One the second point, I nested in the "DAYS" formula to replace the "$I7-$I6" calculation. The finished formula is =IFERROR((IF(AND($B15="Shopper", $C15="Completed"),DAYS($I16,$I15),IF(AND($B15="", $C15=""),DAYS($I16,$I15),"UNDEFINED"))),"").

This, however, only yields the time difference in whole days, whereas I would like to know the exact time difference. Nevertheless, this is a good short hand and I'm glad to learn it as an option.

So, is it better to go with this formula? =IFERROR((IF(AND($B6="Shopper", $C6="Completed"),$I7-$I6,IF(AND($B6="", $C6=""),$I7-$I6,"UNDEFINED"))),"")

On your last point, I'm not 100 percent clear on what you are asking, but, yes, I do want to stop calculating the individual time differences for each event when the Reviewer finally "finalizes" the report. Basically, I want to know the time difference between the first time the "Shopper" "Completes" the report and every other event by the "Reviewer" up until the "Reviewer" Finalizes the report for a given Job ID.

There are different reviewers for different job IDs, so I don't want the formula/calculations to overlap, if that makes sense.

I hope this helps.

 

[C with no eyes]

OK. I'm done.

I was about to paste it but thought of one more question...

Will everything on this report be locked? i.e. no open cases, or whatever

 

[C with no eyes]

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.

 

[mhighland]

OK. I'm done.

I was about to paste it but thought of one more question...

Will everything on this report be locked? i.e. no open cases, or whatever

BY "Locked," are you talking about the "Status" column? If so, then the vast majority of reports will be "Locked," but not all. Also, some reports are "Locked" multiple times in subsequent events.

"Finalized" is the most important end status.

I'll try the formulas you provided in the post below and report back.

Thanks for the help.