google-sheetssplitgoogle-sheets-formulafrequencyschedule

Creating a frequency schedule that updates dynamically based on start date & cadence

I'm looking to create a frequency schedule that highlights when each activity should be performed throughout the year based on the dropdown cadence (daily, monthly, quarterly, semi-annually) and the start date. So if it's daily, it should repeat on every day after the start date. If monthly, every month after the start date and so on.

The attached image is what the desired result would be.

enter image description here

I can figure out the daily easily enough using the rept & split function but I'm having a hard time figuring out how to skip say 30/31 cells for the monthly and more for the quarterly and semi-annually based on the dynamic cadence & start date.

Sample sheet access https://docs.google.com/spreadsheets/d/1-luCbmldWuGBEJJBYtHnBlFs60Ol5h7OIdNHdZKs3wM/edit?usp=sharing

Solution

  • Here's one approach you may test out:

    =let(Σ,index(match(,0/(A:A<>""))),map(A5:index(A:A,Σ),B5:index(B:B,Σ),lambda(a,b,if(or(a="",b=""),,map(C3:3,lambda(c,let(Λ,max(C3:3),
 if(a="Daily",if(vlookup(c,sequence(Λ-b+1,1,b),1,),C1),
 if(ifna(vlookup(c,sort(edate(b,sequence(datedif(b,Λ,"m")+1,1,0,switch(a,"Monthly",1,"Quarterly",3,"Semi-Annually",6)))),1,)),C1,)))))))))

    enter image description here