Dynamic List of Weekday Dates in Excel

Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.

In the image below we have a start date and a number of weekdays in the yellow input cells.

The formula in cell D1 is creating a list of dates in a spill range.

I have added a formula in column E2 display the day of the week for the date in column D.

You can see we have a Saturday and a Sunday in the middle of the list.

To create a list of weekdays we can use the LET function to capture the daily list and then manipulate the list to only show weekdays.

The formula in cell G1 is.


The variable d captures our list of dates.

The variable w uses an IF function to identify the weekends. Weekends are displayed as blanks.

We then use the FILTER function on the w variable and exclude any blanks. This will leave a list of weekdays in a spill range.

The WEEKDAY function with the 2 as the second argument numbers the days from Monday =1 to Sunday = 7. This means numbers above 5 are the weekend.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.