Calculate date of response

[Trebor8484]

Hi, I am trying to find a way to track how quickly we respond to our engineer queries in working days (Monday - Friday).

When a query comes in it goes into an Open status. When it is responded to it goes into another status which could be a number of different statuses, i.e. Pended/Closed etc.

So in my example it would need to display 1 working day to respond the first engineer and 2 days for the second. The formula would only need to apply for rows showing as Open status as that would always relate to a new query.

Thanks

 

[Toadstool]

Hi Trebor8484,

I think you need a unique query identifier. I can imagine Stu may open another query on 3/1/2021 which may get answered before or after the one on 1/1/2021 so you've challenges with overlap and multiple queries for an Engineer.

 

[Trebor8484]

Thanks, I do have a unique Job no which I can incorporate if that makes it easier

 

[Toadstool]

So something like this:

Trebor8484.xlsx
	A	B	C	D	E	F	G	H
1	Date	Engineer	Time Logged	Status	Job No.	Days Open	Average=	12.3
2	01-Jan-21	Stu	10:00	Open	1
3	01-Jan-21	Bob	10:00	Open	24
4	04-Jan-21	Chris	10:00	Open	3
5	07-Jan-21	Bob	10:00	Closed	22	No matching Open
6	07-Jan-21	Stu	10:00	Open	2
7	07-Jan-21	Chris	10:00	Pended	3
8	07-Jan-21	Bob	10:00	Closed	24	5
9	19-Jan-21	Stu	10:00	Closed	1	13
10	22-Jan-21	Chris	10:00	Closed	3	15
11	28-Jan-21	Stu	10:00	Closed	2	16
12
Sheet1

Cell Formulas
Range	Formula
H1	H1	=AVERAGE($F$2:$F$9999)
F2:F12	F2	=IF(D2<>"Closed","",IFERROR(NETWORKDAYS(INDEX($A$2:$A$9999,MATCH(1,INDEX((B2=$B$2:$B$9999)*("Open"=$D$2:$D$9999)*(E2=$E$2:$E$9999),0,1),0)),A2),"No matching Open"))