Creating automated schedule with specific weekdays and daily frequency in Google Sheets

Other Questions Google Sheets
1

I’m trying to build a spreadsheet that creates recurring schedules automatically. I have tasks that need to happen on certain days of the week and specific times during the day.

My Setup

Task Dawn Midday Dusk Late Mon Tue Wed Thu Fri Sat Sun Count 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
Task D 1 1 1 3

What I want to get:

Date Task Time
10.12.24 Task A Dawn
10.12.24 Task A Dusk
10.12.24 Task B Midday
11.12.24 Task B Midday

The Problem

Some tasks work fine when they can happen any day. But others have specific weekday rules. For example, Task C only happens on Monday, Wednesday, and Friday. If my start date is a Tuesday, it should skip to Wednesday instead.

I tried using formulas like =StartDate+MOD(WeekdayNumber-WEEKDAY(StartDate,2),7) but it doesn’t work with ARRAYFORMULA. I need everything to update automatically without dragging formulas down.

How can I make Google Sheets generate the right dates that match both the weekday restrictions and repeat the correct number of times?

2

this is tricky with arrayformula. try using query with a helper column - generate all possible dates first, then filter by weekday. i’ve tackled similar problems but honestly ended up using apps script for complex scheduling. way easier than wrestling with nested formulas.

3

I split this into stages instead of cramming everything into one massive ARRAYFORMULA. Here’s what worked: I made a separate calculation sheet with date sequences, then used FILTER + WEEKDAY functions. First, generate your full date range for the schedule period. Then add helper columns to check if each date hits your weekday requirements - something like =IF(WEEKDAY(A2,2)=1,IF(B$2=1,A2,"") ,"") for Mondays. After that, FILTER pulls only the valid combinations. It’s messier than one formula but actually works reliably and updates automatically when you tweak parameters. The trick is accepting that complex scheduling needs multiple steps - don’t try to force it all into one mega-formula.