This blog post aims at generating only a specific weekday from a week in Excel. By employing the SEQUENCE, FILTER, and WEEKDAY functions, we can tailor our date lists to fit our specific scheduling needs, read to learn how! (you’ll be amazed to see how simple it is!)
Consider a scenario where start date is in cell H2, we need to generate a particular weekday as specified in H4.
In our example, we need to create every Wednesday (4th day), starting from 05-01-2023.
Note: By default, Excel considers Sun=1, Mon=2, and so on.
Now, to begin, we’ll use the SEQUENCE function to generate 365 days (in cell H3) starting from 05-01-2023 (in cell H2):
=SEQUENCE(H3, 1, H2, 1)the syntax for which is:
This function takes as input, the rows to create, columns to create, the starting point, and the steps to increment each time. In our case, this generates 365 days from the given start date.
Secondly, we’ll use the FILTER function to filter out only the 4th weekday (in cell H4), the formula in cell H6 would look like this:
=FILTER(SEQUENCE(H3,1,H2,1),WEEKDAY(SEQUENCE(H3,1,H2,1))=H4)This formula first uses the SEQUENCE function to generate all days from the given start date and then the FILTER function returns only the weekday that equals the weekday we specified in H4.
Syntax for FILTER is
Thirdly, inside the FILTER, we’ve used the WEEKDAY function, which returns the day of a given date. Because, we need to generate only a specific weekday as in cell H4) and its syntax is
Most importantly, please note that this function returns only the number of chosen weekdays within the 365 days; it does not cover the entire 365 days.
To understand this clearly, use the TEXT function here to return the day of the week in the adjacent cell.
=TEXT(H6,"ddd")We generate our sequence of dates for every Wednesday as follows.
Check our step-by-step explainer video on creating dates with skips under various scenarios here:
If you have any feedback or suggestions, please post them in the comments below.
