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.

=LET(d,B1+SEQUENCE(B2)-1,w,IF(WEEKDAY(d,2)>5,"",d),FILTER(w,w<>""))

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.