0

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

Improve this question
3
  • You need to find a function that returns the date, given the start date and the number of days from the start date. No idea what that would be in "google-sheets", but in C++ it is in std::chrono. Recommend: stop mucking around in the cloud and write an application in your favorite coding language. Commented May 20, 2024 at 15:12
  • It would help if you could share a sample sheet with realistic-demo data and expected output(manually entered) so as for us to get a clear picture & workout a solution. You may also use the anonymous blank sheet maker provided by docs community forum incase you do not wish to share a sheet from your google account. Commented May 20, 2024 at 15:33
  • 1
    I've added a sample sheet with the desired results. Commented May 20, 2024 at 16:06

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@ravenspoint I agree that rockinfreakshow's code could be pretty-printed to make it easier on the eye, but see nothing wrong with the logic. Spreadsheet formula languages constitute functional programming that differs from the imperative and object-oriented models. See Lambda calculus.
0

alternative:

=INDEX(BYROW(A5:INDEX(A:A, MAX(ROW(A:A)*(A:A<>""))), 
 LAMBDA(a, LET(b, OFFSET(a,,1), d, C3:3, IF(((a="Daily")*(b<=d))+
 ISNUMBER(MATCH(d, EDATE(b, SEQUENCE(1, DATEDIF(MIN(d), MAX(d), "M"),, 
 SWITCH(a, "Monthly", 1, "Quarterly", 3, "Semi-Annually", 6))), )), C1, )))))

enter image description here

spreadsheet demo

bonus...

fill with dates from date x till last column:

=SEQUENCE(1, COLUMNS(C3:3), "2024-01-01")

fill with dates from date x to date y:

=SEQUENCE(1, ABS("2024-01-01"-"2024-12-31")+1, "2024-01-01")

fill day names:

=INDEX(IF(C3:3="",,TEXT(C3:3, "ddd")))
Improve this answer

1 Comment

This worked wonderfully, but I'm now having the issue of having to add in a weekly category (column A). The EDATE function doesn't seem to work with weekly as far as I can tell. I tried using just DAYS and adjusting the SWITCH parameters, but was having an issue tying that with the Start Date (column B) so I'm not sure what I missed. Any suggestions?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.