VBA Calculate Turn-around Time

B

bokboxes

New Member
Joined
Apr 18, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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".

1621409123682.png
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Because of Somproduct formula, Second method maybe take long time to respond if you data is big.
The Data at Column J is based of Month of Column A (Starting Date) & at Column L is Based Month of Data At Column E
Book1.xlsx
AEFGHIJKLM
1Month No.Month NameAverage number of days took to CompleteAverage number of days took to Complete
24/14/20214/17/20214April2.942.93
34/15/20214/17/20215May3.063.05
44/16/20214/20/2021
54/17/20214/20/2021
64/18/20214/20/2021Month No.Month NameAverage number of days took to CompleteAverage number of days took to Complete
74/19/20214/23/20214April2.942.93
84/20/20214/23/20215May3.063.05
94/21/20214/23/2021
104/22/20214/26/2021
114/23/20214/26/2021
124/24/20214/26/2021
134/25/20214/29/2021
144/26/20214/29/2021
154/27/20214/29/2021
164/28/20215/2/2021
174/29/20215/2/2021
184/30/20215/2/2021
195/1/20215/5/2021
205/2/20215/5/2021
215/3/20215/5/2021
225/4/20215/8/2021
235/5/20215/8/2021
245/6/20215/8/2021
255/7/20215/11/2021
265/8/20215/11/2021
275/9/20215/11/2021
285/10/20215/14/2021
295/11/20215/14/2021
305/12/20215/14/2021
315/13/20215/17/2021
325/14/20215/17/2021
335/15/20215/17/2021
345/16/20215/20/2021
355/17/20215/20/2021
365/18/20215/21/2021
37
List
Cell Formulas
RangeFormula
H2,H7H2=MONTH(TODAY())-1
I2,I7I2=TEXT(EOMONTH(TODAY(),-1),"MMMM")
J2:J3J2=(SUMPRODUCT(($E$2:$E$36)*(MONTH($A$2:$A$36)=H2))-SUMPRODUCT(($A$2:$A$36)*(MONTH($A$2:$A$36)=H2)))/SUMPRODUCT(1*(MONTH($A$2:$A$36)=H2))
H3,H8H3=MONTH(TODAY())
I3,I8I3=TEXT(TODAY(),"MMMM")
L2:L3L2=(SUMPRODUCT(($E$2:$E$36)*(MONTH($E$2:$E$36)=H2))-SUMPRODUCT(($A$2:$A$36)*(MONTH($E$2:$E$36)=H2)))/SUMPRODUCT(1*(MONTH($E$2:$E$36)=H2))
J7:J8J7=(SUMPRODUCT((E:E)*(MONTH(A:A)=H7))-SUMPRODUCT((A:A)*(MONTH(A:A)=H7)))/SUMPRODUCT(1*(MONTH(A:$A)=H7))
L7:L8L7=(SUMPRODUCT((E:E)*(MONTH(E:$E)=H7))-SUMPRODUCT((A:A)*(MONTH(E:E)=H7)))/SUMPRODUCT(1*(MONTH(E:E)=H7))
E2,E35:E36,E32,E29,E26,E23,E20,E17,E14,E11,E8,E5E2=A2+3
E3,E33,E30,E27,E24,E21,E18,E15,E12,E9,E6E3=A3+2
E4,E34,E31,E28,E25,E22,E19,E16,E13,E10,E7E4=A4+4
 
Upvote 0
Also this one. this is quicker than previous method:
Book1.xlsx
EFGHIJKLM
1Month No.Month NameAverage number of days took to CompleteAverage number of days took to Complete
24/17/20214April2.942.93
34/17/20215May3.063.05
44/20/2021
54/20/2021
64/20/2021
74/23/2021
84/23/2021
94/23/2021
104/26/2021
114/26/2021
124/26/2021
134/29/2021
144/29/2021
154/29/2021
165/2/2021
175/2/2021
185/2/2021
195/5/2021
205/5/2021
215/5/2021
225/8/2021
235/8/2021
245/8/2021
255/11/2021
265/11/2021
275/11/2021
285/14/2021
295/14/2021
305/14/2021
315/17/2021
325/17/2021
335/17/2021
345/20/2021
355/20/2021
365/21/2021
37
List
Cell Formulas
RangeFormula
H2H2=MONTH(TODAY())-1
I2I2=TEXT(EOMONTH(TODAY(),-1),"MMMM")
J2J2=AVERAGEIFS(E:E,A:A,">"&EOMONTH(TODAY(),-2),A:A,"<" & EOMONTH(TODAY(),-1) + 1)-AVERAGEIFS(A:A,A:A,">"&EOMONTH(TODAY(),-2),A:A,"<" & EOMONTH(TODAY(),-1) + 1)
H3H3=MONTH(TODAY())
I3I3=TEXT(TODAY(),"MMMM")
J3J3=AVERAGEIFS(E:E,A:A,">"&EOMONTH(TODAY(),-1),A:A,"<" & EOMONTH(TODAY(),0) + 1)-AVERAGEIFS(A:A,A:A,">"&EOMONTH(TODAY(),-1),A:A,"<" & EOMONTH(TODAY(),0) + 1)
L2L2=AVERAGEIFS(E:E,E:E,">"&EOMONTH(TODAY(),-2),E:E,"<" & EOMONTH(TODAY(),-1) + 1)-AVERAGEIFS(A:A,E:E,">"&EOMONTH(TODAY(),-2),E:E,"<" & EOMONTH(TODAY(),-1) + 1)
L3L3=AVERAGEIFS(E:E,E:E,">"&EOMONTH(TODAY(),-1),E:E,"<" & EOMONTH(TODAY(),0) + 1)-AVERAGEIFS(A:A,E:E,">"&EOMONTH(TODAY(),-1),E:E,"<" & EOMONTH(TODAY(),0) + 1)
E2,E35:E36,E32,E29,E26,E23,E20,E17,E14,E11,E8,E5E2=A2+3
E3,E33,E30,E27,E24,E21,E18,E15,E12,E9,E6E3=A3+2
E4,E34,E31,E28,E25,E22,E19,E16,E13,E10,E7E4=A4+4
 
Upvote 0
Solution
maabadi said:
Also this one. this is quicker than previous method:
Book1.xlsx
EFGHIJKLM
1Month No.Month NameAverage number of days took to CompleteAverage number of days took to Complete
24/17/20214April2.942.93
34/17/20215May3.063.05
44/20/2021
54/20/2021
64/20/2021
74/23/2021
84/23/2021
94/23/2021
104/26/2021
114/26/2021
124/26/2021
134/29/2021
144/29/2021
154/29/2021
165/2/2021
175/2/2021
185/2/2021
195/5/2021
205/5/2021
215/5/2021
225/8/2021
235/8/2021
245/8/2021
255/11/2021
265/11/2021
275/11/2021
285/14/2021
295/14/2021
305/14/2021
315/17/2021
325/17/2021
335/17/2021
345/20/2021
355/20/2021
365/21/2021
37
List
Cell Formulas
RangeFormula
H2H2=MONTH(TODAY())-1
I2I2=TEXT(EOMONTH(TODAY(),-1),"MMMM")
J2J2=AVERAGEIFS(E:E,A:A,">"&EOMONTH(TODAY(),-2),A:A,"<" & EOMONTH(TODAY(),-1) + 1)-AVERAGEIFS(A:A,A:A,">"&EOMONTH(TODAY(),-2),A:A,"<" & EOMONTH(TODAY(),-1) + 1)
H3H3=MONTH(TODAY())
I3I3=TEXT(TODAY(),"MMMM")
J3J3=AVERAGEIFS(E:E,A:A,">"&EOMONTH(TODAY(),-1),A:A,"<" & EOMONTH(TODAY(),0) + 1)-AVERAGEIFS(A:A,A:A,">"&EOMONTH(TODAY(),-1),A:A,"<" & EOMONTH(TODAY(),0) + 1)
L2L2=AVERAGEIFS(E:E,E:E,">"&EOMONTH(TODAY(),-2),E:E,"<" & EOMONTH(TODAY(),-1) + 1)-AVERAGEIFS(A:A,E:E,">"&EOMONTH(TODAY(),-2),E:E,"<" & EOMONTH(TODAY(),-1) + 1)
L3L3=AVERAGEIFS(E:E,E:E,">"&EOMONTH(TODAY(),-1),E:E,"<" & EOMONTH(TODAY(),0) + 1)-AVERAGEIFS(A:A,E:E,">"&EOMONTH(TODAY(),-1),E:E,"<" & EOMONTH(TODAY(),0) + 1)
E2,E35:E36,E32,E29,E26,E23,E20,E17,E14,E11,E8,E5E2=A2+3
E3,E33,E30,E27,E24,E21,E18,E15,E12,E9,E6E3=A3+2
E4,E34,E31,E28,E25,E22,E19,E16,E13,E10,E7E4=A4+4
Click to expand...
Thanks for the help. I don't get why there is a difference in 0.01 days. Could you kindly explain?
 
Upvote 0
maabadi said:
The Data at Column J is based of Month of Column A (Starting Date) & at Column L is Based Month of Data At Column E
Click to expand...
I told before. Because of some rows have different month at column A & E then you decide to count based column A or Column E. I do it at Column J based month of Column A & Column K based Column E.
 
Upvote 0
maabadi said:
I told before. Because of some rows have different month at column A & E then you decide to count based column A or Column E. I do it at Column J based month of Column A & Column K based Column E.
Click to expand...
Hi, the numbers don't add up. Can I reaffirm it was as such,

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

bokboxes said:
the numbers don't add up
Click to expand...
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.
 
Upvote 0
maabadi said:
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.
Click to expand...
Yup. got it. Thank you so much for helping!
 
Upvote 0
maabadi said:
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.
Click to expand...
So sorry, if I wanna exclude weekends?

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

That extended over the weekends. And I need to exclude weekends.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
229
Yaeger38
Y
B
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
153
Burrgogi
B
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
444
Legacy 352679
L
J
  • Solved
Newbie help for a good cause, please
2 3 4
Replies
38
Views
1K
mumps
mumps
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
459
AndyEd
A

Forum statistics

Threads
1,215,603
Messages
6,125,786
Members
449,259
Latest member
rehanahmadawan

Share this page

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
Back
Top