Calculating the average handling time to complete a task

[lorrry]

Hi,

I'm stuck with a particular kind of formula in excel. I'm looking to calculate the average amount of days it takes for a transaction to be completed for each month. I have a sheet listing when all the expressions of interest and the date/time that this occurred and another listing when the transaction was completed with the date/time. Some expressions of interests never pan out, therefore don't appear in the completed transactions.

For example, I have the data

Expression of interest:

Business1	4/4/2019
Business2	4/16/2019
Business3	4/25/2019
Business4	5/1/2019
Business5	5/3/2019

<tbody>
</tbody>

Completed transaction:

Business2	4/20/2019
Business3	4/27/2019
Business4	5/3/2019

<tbody>
</tbody>

Desired data:

April 2019	=Average days taken to complete transaction in April 2019
May 2019	=Average days taken to complete transaction in May 2019

<tbody>
</tbody>

Does anyone have any guidance of how I should approach this?

 

[AlanY]

note that I'd swapped day/month from your data for testing

Book1
	A	B	C	D	E	F	G	H
1	Expression of interest:			Completed transaction:				Average
2	Business1	04/04/2019		Business2	20/04/2019		Apr-19	3
3	Business2	16/04/2019		Business3	27/04/2019		May-19	2
4	Business3	25/04/2019		Business4	03/05/2019
5	Business4	01/05/2019
6	Business5	03/05/2019
Sheet2

Cell Formulas
Range	Formula
H2	=(SUMPRODUCT($E$2:$E$4,--(MONTH($E$2:$E$4)&YEAR($E$2:$E$4)=MONTH($G2)&YEAR($G2)))-SUMPRODUCT($B$2:$B$6,--(ISNUMBER(MATCH($A$2:$A$6,D:D,0))),--(MONTH($B$2:$B$6)&YEAR($B$2:$B$6)=MONTH($G2)&YEAR($G2))))/SUMPRODUCT(--(MONTH($E$2:$E$4)&YEAR($E$2:$E$4)=MONTH($G2)&YEAR($G2)))

 

[levy77]

I took a slightly different approach but very similar to above. Mine just adds one extra column than above.

https://drive.google.com/open?id=1n0UoQQLi6FqAbCE9S67nn-n3um18cQKe

Any feedback would be much appreciated.

 

[levy77]

I have just had a play around with this formula and it is a great way of going about the problem. However, I could not get it to calculate the days between if the EOI was the previous month to the completed transaction. E.g. EOI on 1/7/2019 and Completed Transaction on 1/8/2019. The formula will not calculate this.

 

[lorrry]

Wow!

Thanks so much for responding so quickly guys. Really appreciate your help. I'll apply both of these formulas and see what works best.