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

mhighland

New Member
Joined
Oct 11, 2017
Messages
13
I'm working with the following table:

EVENTIDPAGESTATUSTYPEJOBIDTIMESTAMPTime Elapsed: DaysTime Elapsed: Hours
49239521SchedulerCreatedPrivate6622643Wednesday, June 28, 2017UNDEFINEDUNDEFINED
49239522SchedulerNewAssigned6622643Wednesday, July 5, 2017UNDEFINEDUNDEFINED
50682764ShopperIncompleteSubmitted6622643Thursday, September 21, 2017UNDEFINEDUNDEFINED
50682775ShopperCompletedSubmitted6622643Thursday, September 21, 201703:10:29UNDEFINED
ReviewerIncompleteSubmitted6622643Monday, September 25, 2017UNDEFINED03:10:29:13
50725746ReviewerCompletedSubmitted6622643Monday, September 25, 201700:22:15UNDEFINED
50746671ReviewerFinalizedSubmitted6622643Tuesday, September 26, 2017UNDEFINEDUNDEFINED
50752728Locked6622643Tuesday, September 26, 2017UNDEFINEDUNDEFINED
49267081SchedulerCreatedPrivate6630464Wednesday, June 28, 2017UNDEFINEDUNDEFINED
49267082SchedulerNewAssigned6630464Friday, July 7, 2017UNDEFINEDUNDEFINED
50335478ShopperIncompleteSubmitted6630464Monday, September 4, 2017UNDEFINEDUNDEFINED
50446072ShopperIncompleteSubmitted6630464Saturday, September 9, 2017UNDEFINEDUNDEFINED
50446074ShopperCompletedSubmitted6630464Saturday, September 9, 201702:02:47UNDEFINED
50471615ReviewerFinalizedSubmitted6630464Monday, September 11, 2017UNDEFINED02:02:47:54
50473794Locked6630464Monday, September 11, 2017UNDEFINEDUNDEFINED

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

50682775ShopperCompleted
50722162ReviewerIncomplete
50725746ReviewerCompleted

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mhighland

New Member
Joined
Oct 11, 2017
Messages
13
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

Board Regular
Joined
Nov 26, 2016
Messages
151
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

Board Regular
Joined
Nov 26, 2016
Messages
151

ADVERTISEMENT

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

New Member
Joined
Oct 11, 2017
Messages
13
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.


ABCDHI
EVENTIDPAGESTATUSTYPEJOBIDTIMESTAMP
49239521SchedulerCreatedPrivate66226436/28/17 06:56
49239522SchedulerNewAssigned66226437/5/17 21:42
50682764ShopperIncompleteSubmitted66226439/21/17 19:21
50682775ShopperCompletedSubmitted66226439/21/17 19:22
50722162ReviewerIncompleteSubmitted66226439/25/17 05:52
50725746ReviewerCompletedSubmitted66226439/25/17 09:19
50746671ReviewerFinalizedSubmitted66226439/26/17 07:34
50752728Locked66226439/26/17 12:19
49267081SchedulerCreatedPrivate66304646/28/17 06:56
49267082SchedulerNewAssigned66304647/7/17 06:48
50335478ShopperIncompleteSubmitted66304649/4/17 09:25
50446072ShopperIncompleteSubmitted66304649/9/17 00:13
50446074ShopperCompletedSubmitted66304649/9/17 00:14
50471615ReviewerFinalizedSubmitted66304649/11/17 03:01
50473794Locked66304649/11/17 06:12

<tbody>
</tbody>


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

Sorry for all the back and forth!
 

mhighland

New Member
Joined
Oct 11, 2017
Messages
13

ADVERTISEMENT

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

Board Regular
Joined
Nov 26, 2016
Messages
151
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

Board Regular
Joined
Nov 26, 2016
Messages
151
How's this...

EVENTIDPAGESTATUSTYPEJOBIDTIMESTAMPTime Elapsed: DaysTime Elapsed: Hours
49239521SchedulerCreatedPrivate662264328/06/17 06:56:00
49239522SchedulerNewAssigned662264305/07/17 21:42:00
50682764ShopperIncompleteSubmitted662264321/09/17 19:21:00
50682775ShopperCompletedSubmitted662264321/09/17 19:22:0003:10:30
50722162ReviewerIncompleteSubmitted662264325/09/17 05:52:0003:10:30:00
50725746ReviewerCompletedSubmitted662264325/09/17 09:19:0003:13:57:00
50746671ReviewerFinalizedSubmitted662264326/09/17 07:34:0004:12:12:00
50752728Locked662264326/09/17 12:19:0004:16:57:00
49267081SchedulerCreatedPrivate663046428/06/17 06:56:00
49267082SchedulerNewAssigned663046407/07/17 06:48:00
50335478ShopperIncompleteSubmitted663046409/04/17 09:25:00
50446072ShopperIncompleteSubmitted663046409/09/17 00:13:00
50446074ShopperCompletedSubmitted663046409/09/17 00:14:0001:02:47
50471615ReviewerFinalizedSubmitted663046409/11/17 03:01:0001:02:47:00
50473794Locked663046409/11/17 06:12:0001: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.
 
Last edited:

mhighland

New Member
Joined
Oct 11, 2017
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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
Top