VBA Calculate Turn-around Time

[bokboxes]

Hi all,

Column A shows entries date and Column E shows completion date.
I need to average the number of days I took to complete, from entry to completion within a calendar month.

The list below is constantly populated and all are sort by Column A dates.

Two things I need to display,

Average number of days took to complete for previous month.
Average number of days took to complete in the current month.

That will continue for the subsequent months and not limited to April & May only as shown below.

Assumingly both value to be displayed in column K1 & K2.
The value to be in 1 decimal place.

These data in in sheet "TAT".

 

[maabadi]

I don't understand exactly. Are your weekend day only Thursday? Or ....

 

[bokboxes]

My formula at Post 3, Column J is appropriate for you with this example that you show.


With that formula , I calculate the difference between average of Column A & Column E based Month at Column A. And this is exact what you want.

Kindly refer to post #3, row 10 for example.

That extended over the weekends. And I need to exclude weekends.

I don't understand exactly. Are your weekend day only Thursday? Or ....

weekend days are Saturday & Sunday. We probably in different time zone.
lets say,

Workdays are Monday to Friday.
Non-workdays are Saturday & Sunday

Assuming,
Start day = Friday
End day = Tuesday

That technically is 5 days but in fact, weekends are not included.
So Turnaround Time = 3 days. (Friday, Monday & Tuesday)

Can we do that?

 

[maabadi]

I calculate it with Networkday. But Networkday calculate one day more than you need I think. ( Test Networkdays function yourself to see result).
If I correct, Use formula at Column N, if you want exact days that calculate Networkdays function, Use Column J Formula.
With this situation, Only Formula way I think is use Sumproduct, And for calculate at total column take time to respond. if your data not Large change all column value to Range you have Data. Example if your Data is in Rows 2:50 Change all A:A to $A$2:$A$50 & E:E to $E$2:$E$50.
if you want we can also define macro for it also, that one should be more quicker.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Month No.	Month Name	Average number of days took to Complete		Average number of days took to Complete
2	4/14/2021				4/17/2021			4	April	2.764706		1.764706
3	4/15/2021				4/17/2021			5	May	3.055556		2.055556
4	4/16/2021				4/20/2021
5	4/17/2021				4/20/2021
6	4/18/2021				4/20/2021
7	4/19/2021				4/23/2021
8	4/20/2021				4/23/2021
9	4/21/2021				4/23/2021
10	4/22/2021				4/26/2021
11	4/23/2021				4/26/2021
12	4/24/2021				4/26/2021
13	4/25/2021				4/29/2021
14	4/26/2021				4/29/2021
15	4/27/2021				4/29/2021
16	4/28/2021				5/2/2021
17	4/29/2021				5/2/2021
18	4/30/2021				5/2/2021
19	5/1/2021				5/5/2021
20	5/2/2021				5/5/2021
21	5/3/2021				5/5/2021
22	5/4/2021				5/8/2021
23	5/5/2021				5/8/2021
24	5/6/2021				5/8/2021
25	5/7/2021				5/11/2021
26	5/8/2021				5/11/2021
27	5/9/2021				5/11/2021
28	5/10/2021				5/14/2021
29	5/11/2021				5/14/2021
30	5/12/2021				5/14/2021
31	5/13/2021				5/17/2021
32	5/14/2021				5/17/2021
33	5/15/2021				5/17/2021
34	5/16/2021				5/20/2021
35	5/17/2021				5/20/2021
36	5/18/2021				5/21/2021
37
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MONTH(TODAY())-1
I2	I2	=TEXT(EOMONTH(TODAY(),-1),"MMMM")
J2	J2	=SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())-1))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())-1))
H3	H3	=MONTH(TODAY())
I3	I3	=TEXT(TODAY(),"MMMM")
J3	J3	=SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())))
L2	L2	=(SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())-1))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())-1)))-1
L3	L3	=(SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY()))))-1
E2,E35:E36,E32,E29,E26,E23,E20,E17,E14,E11,E8,E5	E2	=A2+3
E3,E33,E30,E27,E24,E21,E18,E15,E12,E9,E6	E3	=A3+2
E4,E34,E31,E28,E25,E22,E19,E16,E13,E10,E7	E4	=A4+4

 

[bokboxes]

I calculate it with Networkday. But Networkday calculate one day more than you need I think. ( Test Networkdays function yourself to see result).
If I correct, Use formula at Column N, if you want exact days that calculate Networkdays function, Use Column J Formula.
With this situation, Only Formula way I think is use Sumproduct, And for calculate at total column take time to respond. if your data not Large change all column value to Range you have Data. Example if your Data is in Rows 2:50 Change all A:A to $A$2:$A$50 & E:E to $E$2:$E$50.
if you want we can also define macro for it also, that one should be more quicker.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Month No.	Month Name	Average number of days took to Complete		Average number of days took to Complete
2	4/14/2021				4/17/2021			4	April	2.764706		1.764706
3	4/15/2021				4/17/2021			5	May	3.055556		2.055556
4	4/16/2021				4/20/2021
5	4/17/2021				4/20/2021
6	4/18/2021				4/20/2021
7	4/19/2021				4/23/2021
8	4/20/2021				4/23/2021
9	4/21/2021				4/23/2021
10	4/22/2021				4/26/2021
11	4/23/2021				4/26/2021
12	4/24/2021				4/26/2021
13	4/25/2021				4/29/2021
14	4/26/2021				4/29/2021
15	4/27/2021				4/29/2021
16	4/28/2021				5/2/2021
17	4/29/2021				5/2/2021
18	4/30/2021				5/2/2021
19	5/1/2021				5/5/2021
20	5/2/2021				5/5/2021
21	5/3/2021				5/5/2021
22	5/4/2021				5/8/2021
23	5/5/2021				5/8/2021
24	5/6/2021				5/8/2021
25	5/7/2021				5/11/2021
26	5/8/2021				5/11/2021
27	5/9/2021				5/11/2021
28	5/10/2021				5/14/2021
29	5/11/2021				5/14/2021
30	5/12/2021				5/14/2021
31	5/13/2021				5/17/2021
32	5/14/2021				5/17/2021
33	5/15/2021				5/17/2021
34	5/16/2021				5/20/2021
35	5/17/2021				5/20/2021
36	5/18/2021				5/21/2021
37
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MONTH(TODAY())-1
I2	I2	=TEXT(EOMONTH(TODAY(),-1),"MMMM")
J2	J2	=SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())-1))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())-1))
H3	H3	=MONTH(TODAY())
I3	I3	=TEXT(TODAY(),"MMMM")
J3	J3	=SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())))
L2	L2	=(SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())-1))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY())-1)))-1
L3	L3	=(SUMPRODUCT(NETWORKDAYS(A:A+0,E:E+0)*(MONTH(A:A)=MONTH(TODAY())))/SUMPRODUCT(1*(MONTH(A:$A)=MONTH(TODAY()))))-1
E2,E35:E36,E32,E29,E26,E23,E20,E17,E14,E11,E8,E5	E2	=A2+3
E3,E33,E30,E27,E24,E21,E18,E15,E12,E9,E6	E3	=A3+2
E4,E34,E31,E28,E25,E22,E19,E16,E13,E10,E7	E4	=A4+4

I think macro will work better.
This is what I have got.

The value got larger compared to previous, and a negative value.

Can we do VBA code then?

 

[maabadi]

For Current month, I think at the last rows you have rows that have data at column A But column E is Empty then , you see Negative Result.
Please tell me at the previous post you want result at Column J format Or Column L format.

 

[maabadi]

This is Macro. You should define You want J column Or L column Values.

After that, I change macro to Worksheet Change event to When you add value at column E, Macro run And recalculate Values you want for last two Month.

Sub TaskWorkDays()
Dim i As Long, Lr As Long, J As String, K As Long, N1 As Long, N2 As Long
Dim M1 As Long, M2 As Long, T1 As String, T2 As String
Lr = Range("E" & Rows.Count).End(xlUp).Row
K = Month(Date)
For i = 2 To Lr
Select Case Month(Range("A" & i).Value)
 Case K - 1
  M1 = M1 + Application.WorksheetFunction.NetworkDays(Range("A" & i).Value, Range("E" & i).Value)
  N1 = N1 + 1
 Case K
  M2 = M2 + Application.WorksheetFunction.NetworkDays(Range("A" & i).Value, Range("E" & i).Value)
  N2 = N2 + 1
 End Select
Next i
Range("J2").Value = M1 / N1
Range("J3").Value = M2 / N2
Range("L2").Value = (M1 / N1) - 1
Range("L3").Value = (M2 / N2) - 1
End Sub