dategoogle-sheetsgoogle-sheets-formulasequencearray-formulas

Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets

Recently, I’ve been dealing with a lot of medical-related things. To make it more manageable, I decided to create a schedule that I could print out, follow, and update as needed.

Input Data

Time of day Weekday Schedule
Item Name Morning Noon Evening Night Mon Tue Wed Thu Fri Sat Sun Times Days
Item 01 1 1 1 1 1 1 1 1 1 1
Item 02 1 1 1 1 1 1 1 1 2
Item 03 1 1 1 3
Item 04 1 1 1 3

Desired output

Each item’s name (from each row in the input) should be displayed based on the number of repeats and times-per-day as a list.

Date Item Name Time of day
10.12.24 Item 01 Morning
10.12.24 Item 01 Evening
10.12.24 Item 02 Noon
11.12.24 Item 02 Noon
11.12.24 Item 03 time not specified
13.12.24 Item 03 time not specified
16.12.24 Item 03 time not specified
15.12.24 Item 04 Morning
15.12.24 Item 04 Night
22.12.24 Item 04 Morning

See how Item01 repeats 2 times (as it has 1 day and repeats 2 times per day, and date stays the same).

Item02 also repeats 2 times, but it has two dates - 10th and 11th, as it is set to repeat 1 time per day for 2 days.

Where it gets interesting

Item03 and Item04 have specific weekdays marked, and their starting dates do not match with them.

Time of day Weekday Schedule
Item Name Morning Noon Evening Night Mon Tue Wed Thu Fri Sat Sun Times Days
Item 03 1 1 1 3
Item 04 1 1 1 3

In the output, Item03 starts not from 10th, but from 11th, and Item04 - from 15th. And then it keeps generating dates that match the allowed range until the repeat limit is reached.

Date Item Name Time of day
11.12.24 Item 03 time not specified
13.12.24 Item 03 time not specified
16.12.24 Item 03 time not specified
15.12.24 Item 04 Morning
15.12.24 Item 04 Night
22.12.24 Item 04 Morning

Earlier I managed to handle “continuous” items (those without specific weekday requirements) by generating a sequence starting from the specified date and calculating repeats based on the number of days * times-per-day.

However, I’m struggling with rows like these that have specific weekday constraints. The solution needs to:

If i single one weekday row out

{1/""/1/""/1/""/""}

transpose it, add a helper column with numbers

{ 1 / 1;
    / 2;
  1 / 3;
    / 4;
  1 / 5;
    / 6;
    / 7; }

and extract a column like

{ 1;
  3;
  5 }

I can run

=X+MOD(Y-WEEKDAY(X;2);7)

against one of those values, where X is desired starting date and Y is a value from column before (1-3-5). You can sort of find the closest valid date (from which you could start generating a sequence).

Problem is,

For example, if desired starting day falls onto sunday (7), and the last valid weekday is friday (5, like in the example above - 1-3-5) it should not break and loop back to 1 and output next valid day (monday in this case)

Due to amount of edits / additions, I'll delete, move or mess up something eventually, and all of the helper columns better be auto-populating.

And then, even if I find a 'closest valid day', i'm not really sure how to proceed in generating a valid range like 15.12.24-15.12.24-22.12.24.

Please, help.

Solution

  • Here's one approach you may test out:

    =let(Σ;tocol(;1);reduce(Σ;O7:index(P:P;match(;0/(C:C<>"")));lambda(a;c;let(Ξ;lambda(x;index(x;row(c)));ifna(vstack(if(iserr(+a);Σ;a);if(c="";Σ;
 let(z;max(1;counta(Ξ(D:G)));Λ;reduce(Σ;let(x;sequence(c*7;1;Ξ(Q:Q));filter(x;xmatch(weekday(x;2);filter(sequence(1;7);Ξ(H:N)))));lambda(f;q;vstack(f;
 chooserows({q\Ξ(C:C)};sequence(max(1;counta(Ξ(D:G)));1;1;0)))));let(Γ;tocol(map(ifna(filter(D6:G6;Ξ(D:G));"- time not specified -");lambda(Δ;wrapcols(Δ;c;Δ))));
 chooserows(hstack(Λ;Γ);sequence(c*if(index(6:6;column(c))="Days";z;1))))))))))))

    enter image description here