Cycle/Average time Per QTR Between two dates.

Plukey

New Member
Joined
Apr 19, 2019
Messages
49
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))
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Plukey

New Member
Joined
Apr 19, 2019
Messages
49
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,350
Members
410,828
Latest member
A9Bosv3
Top