Calculate turnaround time based on multiple criteria

[darejack2001]

Hi Everyone,

I need to calculate cumulative time taken by a specific 'Role' to take specific / multiple 'Actions'.

Refer below grid, I need help to evaluate cumulative turn around time (Number of Days & Hours & Minutes) for 'Approver' / 'Reviewer' - in below example we can see in case of Approver & Requestor there are multiple 'ACTION' involved for which the record was pending under their bucket for respective action.

Please Note: There may or may not be multiple actions for a specific role hence I understand some IF logic will have to be built to evaluate TAT, please advise.

Requestor TAT: Time from when the request was 'RETURNED' by 'Approver' till the request was 'RESUBMITTED' by 'Requestor', i.e. B5-B6.
Approver TAT: Time from when the request was 'SUBMITTED' by 'Requestor' till the request was 'RETURNED' by 'Approver' + Time from when the request was 'RESUBMITTED' by 'Requestor' till the request was 'APPROVED' by 'Approver', i.e. (B6-B7)+(B4-B5).

	B	C	D	E
1​	DATE AND TIME	ACTION	ACTOR ROLE	COMMENTS
2​	26/09/2019 17:12	APPROVED	Department Head	ok
3​	26/09/2019 17:10	APPROVED	Depo Admin	ok
4​	26/09/2019 17:07	APPROVED	Approver	ok
5​	01/09/2019 15:05	RESUBMITTED	Requestor	Have updated details, please approve
6​	30/08/2019 12:12	RETURNED	Approver	Incorrect data, please rectify
7​	29/08/2019 18:23	SUBMITTED	Requestor	Request Submitted