# Cycle/Average time Per QTR Between two dates.

#### Plukey

##### Board Regular
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
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")``````

Replies
20
Views
320
Replies
3
Views
171
Replies
0
Views
151
Replies
0
Views
126
Replies
5
Views
92

### Forum statistics

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.

### Which adblocker are you using?    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

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