Cycle/Average time Per QTR Between two dates.

[Plukey]

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!

=IFERROR(DATEDIF(A2,B2,"d"),"")

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)

=SUMPRODUCT((MONTH(DATA!$W$2:$W$17693)=B1:D1)*(YEAR(DATA!$W$2:$W$17693)=A3))

 

[Plukey]

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..

=IFERROR(DATEDIF(A2,B2,"d"),"")

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

=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")