Calculate date of response

Trebor8484

Board Regular
Joined
Oct 27, 2018
Messages
57
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.


Capture2.PNG


Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Oct 27, 2018
Messages
57
Thanks, I do have a unique Job no which I can incorporate if that makes it easier
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
So something like this:

Trebor8484.xlsx
ABCDEFGH
1DateEngineerTime LoggedStatusJob No.Days OpenAverage=12.3
201-Jan-21Stu10:00Open1 
301-Jan-21Bob10:00Open24 
404-Jan-21Chris10:00Open3 
507-Jan-21Bob10:00Closed22No matching Open
607-Jan-21Stu10:00Open2 
707-Jan-21Chris10:00Pended3 
807-Jan-21Bob10:00Closed245
919-Jan-21Stu10:00Closed113
1022-Jan-21Chris10:00Closed315
1128-Jan-21Stu10:00Closed216
12 
Sheet1
Cell Formulas
RangeFormula
H1H1=AVERAGE($F$2:$F$9999)
F2:F12F2=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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,427
Members
417,025
Latest member
MusterDuster

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