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.
Just noting that you can shorten your two formulas as follows…
D1: =SEQUENCE(B2,,B1)
G1: =LET(d,SEQUENCE(B2,,B1),FILTER(d,WEEKDAY(d,2)<6))
Thanks Rick
I must admit I use the startdate + SEQUENCE() rather than the SEQUENCE(n,,startdate) as it is a bit easier to explain.
Yes FILTER is a better technique to list the entries.
Thanks again Rick