Home > database >  SEQUENCE between two dates, only by named weekdays, skipping number of weeks selected
SEQUENCE between two dates, only by named weekdays, skipping number of weeks selected

Time:11-15

I'm attempting to create a work schedule for contracted work, based on a rotating roster.

I have been able to make the SEQUENCE formula to list days between two dates, stepped by the number of weeks the rotation is set to, but I would like it to only list days of the week selected in the WEEK DAYS range.

At the moment, the date that the formula steps from is always the start date.

For example, although 03/01/2022 (UK) is a Monday, if the WEEKDAY range is selected to be Thursdays and Fridays, with a rotation of 4 weeks, then the result should give me the result:

Start Date
03/01/22

End Date
10/03/22

Week Rotation
8 (weeks)

Weekdays
Thursday
Fridays

Desired result:
06/01/22
07/01/22
10/02/22
11/02/22
07/03/22
08/03/22

The formula I am using so far is:

=SEQUENCE(B8,1,A5,A8*7)

But I feel like with FILTER, there is perhaps a way of me getting the desired result.

Along these lines?
=FILTER(SEQUENCE(B8,1,A5,A8*7),WEEKDAY(SEQUENCE(B8,1,A5,A8*7),2))

Your help would be highly appreciate- I've spent many hours trying to solve this one going forward and backward with all sorts of bulky formulas!!!

Link to live spreadsheet.
enter image description here


update:

=FILTER(SEQUENCE(B5-A5, 1, A5), 
 REGEXMATCH(""&ROUNDUP(SEQUENCE(B5-A5)/7), 
 "\b("&JOIN("|", SEQUENCE(12, 1, 1, A8))&")\b"), 
 REGEXMATCH(TEXT(SEQUENCE(B5-A5, 1, A5), "ddd"), 
 TEXTJOIN("|", 1, LEFT(B11:B15, 3))))

enter image description here

  • Related