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!
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))