Cycle/Average time Per QTR Between two dates.

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
I currently have one worksheet (DATA) that's within a workbook that has 2018,2019,2020 of inventory data. My supervisor asked for cycle time per QTR for the last three years 2018,2019,2020. I have a Start Date (A) & End Date (B) x4. That I need the average/Cycle time between each.

My steps were, 1st Added a helper column (C) and got the total days between dates w/ (=DATEDIF) and then 2nd formula I used (=AVERAGE) comparing End Date (B) and #DAYs (C) I had to filter the years From DATA & copy & paste each to a new tab 2018,2019,2020 because i can't figure out how to specify the "YEAR" in the (=average) formula. I managed to calculate total lines w/ (=SUMPRODUCT) by adding the #Month 1-12 in separate hidden cells with the year in another cell for the (=SUMPRODUCT) formula to work.
My question...Is there a way to combine the 1st two formulas to extract the cycle time between two dates, per qtr, with a specified year with/out adding the helper column and the separate years to new sheets. Because the DATA sheet gets updated weekly & and now they want a Qtr.'y report moving forward. I've completed the task for now, but need it to flow mush easier in the future. Any advice or ideas are appreciated!

Excel Formula:
=IFERROR(DATEDIF(A2,B2,"d"),"")
Excel Formula:
ROUNDUP(AVERAGE(IF(MONTH('2020'!X2:X6133)<4,DATA!Z2:Z6133)),0)
=ROUNDUP(AVERAGE(IF(MONTH('2020'!B2:B6133)>3,IF(MONTH('2018'!B2:B6133)<7,'2020'!C2:C6133))),0)
=ROUNDUP(AVERAGE(IF(MONTH('2020'!B2:B6133)>6,IF(MONTH('2018'!B2:B6133)<10,'2020'!C2:C6133))),0)
=ROUNDUP(AVERAGE(IF(MONTH('2020'!B2:B6133)>9,'2018'!C2:C6133)),0)
Excel Formula:
=SUMPRODUCT((MONTH(DATA!$W$2:$W$17693)=B1:D1)*(YEAR(DATA!$W$2:$W$17693)=A3))
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
I figured it out...Sorry for the long story, but if anyone is working on a similar task to get the average between two dates per QTR with a specified year. Here ya go... And if anyone has a more simplified version please go..

First get the number of days between two dates with..

Excel Formula:
=IFERROR(DATEDIF(A2,B2,"d"),"")

And then use these for Q1,Q2,Q3,Q4 (I2)=Year

Excel Formula:
=IFERROR(AVERAGE(IF(MONTH(B2:B26)<4,IF(YEAR(B2:B26)=I2,C2:C26))),"0")
=IFERROR(AVERAGE(IF(MONTH(B2:B26)>3,IF(MONTH(B2:B26)<7,IF(YEAR(B2:B26)=I2,C2:C26)))),"0")
=IFERROR(AVERAGE(IF(MONTH(B2:B26)>6,IF(MONTH(B2:B26)<10,IF(YEAR(B2:B26)=I2,C2:C26)))),"0")
=IFERROR(AVERAGE(IF(MONTH(B2:B26)>9,IF(YEAR(B2:B26)=I2,C2:C26))),"0")
 

Forum statistics

Threads
1,147,920
Messages
5,743,875
Members
423,823
Latest member
Zxcvbnm58

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