I'm working with the following table:
<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)?
<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.
<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.
