I need some assistance in developing a scheduling system in Sheets for my project. I’m managing tasks that should be scheduled on specific days of the week and at particular times.
Here’s my current setup:
Task
Dawn
Midday
Dusk
Late
M
T
W
T
F
S
S
Repeats
Duration
Task A
1
1
1
1
1
1
1
1
1
1
Task B
1
1
1
1
1
1
1
1
2
Task C
1
1
1
3
Each task has various requirements. Certain tasks need to occur at specific times (like dawn or midday) and only on particular weekdays indicated by 1s.
What I aim to accomplish:
Date
Task
Time
05.01.25
Task A
Dawn
05.01.25
Task A
Dusk
05.01.25
Task B
Midday
06.01.25
Task B
Midday
07.01.25
Task C
unspecified
The challenge arises when the starting date doesn’t align with the allowed weekdays. For instance, if I start on a Tuesday but Task C is only allowed on Mondays, Wednesdays, or Fridays, it should instead start on the next Wednesday.
I attempted to utilize the following formula to identify the next valid date:
=startDate+MOD(allowedDay-WEEKDAY(startDate,2),7)
However, I’m encountering difficulties applying it with several permitted days and I’m not sure how to generate the complete sequence of dates. Additionally, it must function with ARRAYFORMULA to avoid manual formula copying.
How can I write a formula that automatically determines the first valid date and subsequently generates all following dates based on the repeat settings?
tricky one! i’d try FILTER with SEQUENCE. something like =FILTER(SEQUENCE(30,1,startDate),MOD(WEEKDAY(SEQUENCE(30,1,startDate),2)-1,7)+1=allowedDays) might work. haven’t tested it, but the idea’s to generate your sequence then filter by weekday constraints. you’ll probably need to tweak it for multiple allowed days.
Hit this exact problem building a shift scheduler last year. The trick is using FLATTEN with IF arrays for multiple weekday constraints. Don’t try forcing MOD to work with multiple days - I had way better luck with =FLATTEN(IF(ISNUMBER(MATCH(WEEKDAY(date_sequence,2),{1;3;5},0)),date_sequence,"")) where {1;3;5} are your allowed weekdays (Monday, Wednesday, Friday). Build your base date sequence with SEQUENCE first, then filter by weekdays. For time assignments, just VLOOKUP against your task config table inside the same ARRAYFORMULA. Handle repeats by multiplying your SEQUENCE length by duration values. This scales way better than nesting multiple MOD operations and actually works with ARRAYFORMULA across the whole range.
I’ve tackled similar scheduling problems by mixing QUERY with conditional logic. Create a helper column that calculates valid dates using ARRAYFORMULA, then pull your scheduling data from there. Try =ARRAYFORMULA(IF(AND(MOD(WEEKDAY(date_range,2),7)+1,allowed_weekdays),date_range,"")) but you’ll need to handle array comparison differently - AND won’t work with arrays directly. What worked better for me was SUMPRODUCT to check if the weekday matches your allowed days. Handle repeats by expanding your date range based on the duration column and filter from there. For time assignments, use nested IF statements to check which time slots have 1 marked for each task.