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

[mhighland]

I'm working with the following table:

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

<tbody>
</tbody>

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)?

50682775	Shopper	Completed
50722162	Reviewer	Incomplete
50725746	Reviewer	Completed

<tbody>
</tbody>

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...

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

<tbody>
</tbody>

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.

A	B	C	D	E	F	G	H
EVENTID	PAGE	STATUS	TYPE	JOBID	TIMESTAMP	TIME ELAPSED 1	TIME ELAPSED 2
49239521	Scheduler	Created	Private	6622643	6/28/17 06:56
49239522	Scheduler	New	Assigned	6622643	7/5/17 21:42
50682764	Shopper	Incomplete	Submitted	6622643	9/21/17 19:21
50682775	Shopper	Completed	Submitted	6622643	9/21/17 19:22	3.436956019
50722162	Reviewer	Incomplete	Submitted	6622643	9/25/17 05:52
50725746	Reviewer	Completed	Submitted	6622643	9/25/17 09:19
50746671	Reviewer	Finalized	Submitted	6622643	9/26/17 07:34
50752728		Locked		6622643	9/26/17 12:19
49267081	Scheduler	Created	Private	6630464	6/28/17 06:56
49267082	Scheduler	New	Assigned	6630464	7/7/17 06:48
50335478	Shopper	Incomplete	Submitted	6630464	9/4/17 09:25
50446072	Shopper	Incomplete	Submitted	6630464	9/9/17 00:13
50446074	Shopper	Completed	Submitted	6630464	9/9/17 00:14	2.116597222
50471615	Reviewer	Finalized	Submitted	6630464	9/11/17 03:01
50473794		Locked		6630464	9/11/17 06:12

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

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:

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

<tbody>
</tbody>

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