We can list the the workdays, the weekends or all the dates between two dates with Excel formula using SEQUENCE, WEEKDAY and FILTER functions.
In this tutorial:
Alternatively, we can use Excel flash fill feature that helps listing all the dates or weekday only dates excluding weekends by simply dragging the cell containing specific date. Sadly, it doesn’t let us fill weekend only dates.
Another option is to use SEQUENCE tool. Once we provide the starting date, we can list specific number of days and has the option to choose to list just the weekdays excluding weekends. Again, we cannot use SEQUENCE tool to list weekend only dates.
This is why dynamic formula approach to list the dates between dates is best of the bunch.
List All the dates between dates
To get the list of all the dates between two specific dates i.e. start and end date, we can use SEQUENCE function as following:
=SEQUENCE(B3-A3-1,,A3+1)
Remember, SEQUENCE function creates an array of sequential numbers where generated sequence is based on user input. It is a dynamic function, therefore, spills into multiple columns and rows. SEQUENCE function has the following syntax:
=SEQUENCE(rows,[columns],[start],[step])
Again, the formula we used to list all the dates is:
=SEQUENCE(B3-A3-1,,A3+1)B3-A3-1: B3 has the end date and A3 has the start date. Subtracting end date from the start date will give us total number of days between two dates. And as we are listing all the dates between the start and end date i.e. excluding the start and end the date, “-1” is included to the argument to exclude the end date.
If we remove the “date” formatting by changing it to “General” , our dates are just serial numbers formatted in month/day/year, so B3-A3-1 basically means; 46126-46113-1 = 12 days.
Columns argument is skipped.
A3+1: In the start argument, reference to start date is made and to exclude start date, we included +1 to the reference.
Again, as dates are serial numbers, A3+1 means; 46113+1 = 46114
So our actual SEQUENCE formula is basically:
=SEQUENCE(12,,46114)
And if we remove the formatting from the dates the result of our formula is:
SEQUENCE function created the sequence of 12 dates starting with 46114 i.e. 4/2/2026 and ending at 4/13/2026.
To list all the dates between dates including the start and the end date, we simply need to make the start and the end date inclusive by changing the formula as following:
=SEQUENCE(B3-A3+1,,A3)
List Workdays only between dates
After learning the formula that can list all the dates between two dates, we can use WEEKDAY and FILTER function to extract workdays only dates from this list.
The formula can be:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)<6))The first part of the formula is:
=LET(d,SEQUENCE(B3-A3-1,,A3+1)LET function helps us define a short name for a long statement. In our case, the long statement is the SEQUENCE formula we created to list all the dates defined as “d”. That made our formula shorter as we simply need to provide “d” instead of the entire SEQUENCE formula at the required instances.
After the name part we have:
FILTER(d,WEEKDAY(d,2)<6)In this part, we use FILTER function to filter an array named “d” which is the complete list of dates between start and end date using the criteria based on WEEKDAY function.
The criteria part is:
WEEKDAY(d,2)<6WEEKDAY function returns a specific number for each date based on the day of the week. By default, it returns 1 for Sunday, 2 for Monday and so on. But WEEKDAY function also let us select a different starting weekday than the default Sunday. To make Monday as the first day of the week, we selected 2 for the return_type argument. This way 1 is returned for Monday, 2 for Tuesday and so on until 7 for Sunday.
So the WEEKDAY(d,2) part of the criteria returns the number as follows:
In other words, number 1 till 5 means Monday, Tuesday, Wednesday, Thursday and Friday or in other words, workdays dates.
Whereas, number 6 and 7 means Saturday and Sunday or weekends dates.
By appending “<6” to the formula, it will convert the entire weekday array into TRUE/FALSE array. As we are checking each date based on the weekday number if it is a workday or weekend. The dates with weekday numbers less than 6 will return TRUE and the dates with 6 or 7 will return FALSE.
This TRUE/FALSE array acts as a criteria and tells FILTER function to extract only such dates from array named “d” that returns TRUE.
And here is the complete formula once again spilling the workdays only dates:
List Weekends only between dates
To list weekends only dates between two dates, we can use the same formula used to get workdays list by changing “<6” to “>5”. So the formula can be:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)>5))By appending >5 to WEEKDAY part of the formula, we asked to return such dates with weekday number greater than 5. This leave us with weekday numbers 6 and 7 i.e. Saturday and Sunday.
List Saturdays only between dates
To list Saturday only dates between the range of dates, we can adjust the WEEKDAY part in the above formula to equate to digit 6. That way, FILTER function will output the list of only such dates that fall on Saturday.
To get the list of Saturday only dates between two dates the formula in Excel is:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)=6))
List Sundays only between dates
Similarly, the formula to return Sundays only dates between two dates can be:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)=7))
The above formula has two parts under the LET function:
d,SEQUENCE(B3-A3-1,,A3+1): This part refers to the formula, named as d, that generates the sequential list of dates between start (cell A3) and end date (cell B3). Once name is defined, we can use it instead of using the entire formula again.
FILTER(d,WEEKDAY(d,2)=7): This part filters the array named “d” for days that has a weekday number of 7. Where 1 is for Monday, 2 for Tuesday, 3 for Wednesday, 4 for Thursday, 5 for Friday, 6 for Saturday and 7 for Sunday. As we want to list Sunday only, thus equating WEEKDAY formula to 7.
List Dates of Specific day of the week between dates
To list dates of specific weekday between two dates like Monday, Wednesday or Thursday etc. we can adjust the weekday logic to equate to the weekday number specific for such weekday.
With Monday as the first day of the week, the WEEKDAY function returns 1 for Monday, 2 for Tuesday, 3 for Wednesday, 4 for Thursday, 5 for Friday, 6 for Saturday and 7 for Sunday.
List Monday only dates between dates
To list Monday only dates between two dates we can use the following formula in Excel:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)=1))
List Thursdays only dates between dates
Similarly to list Thursday only dates between the two dates the Excel formula can be:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,WEEKDAY(d,2)=4))
List Dates of Specific Days of the week between dates
To list dates of more than one specific day of the week like Monday, Tuesday or Wednesday Thursday etc, we need to use OR criteria in the formula.
To get the list of Monday and Wednesday only dates between two specific dates, the formula will be:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),FILTER(d,(WEEKDAY(d,2)=1)+(WEEKDAY(d,2)=3)))
In the above formula, we can see two statements of WEEKDAY function were used;
- first instance is meant to list Monday only dates
- second instance is meant to list Wednesday only dates
And both criteria are joined with a plus sign which represents OR criteria. In other words, FILTER function is asked extract dates that are either Monday or Wednesday from the array named “d” which is the list of all the dates between start and the end date.
We can shorten the above formula further by adding a new name for WEEKDAY part as following:
=LET(d,SEQUENCE(B3-A3-1,,A3+1),w,WEEKDAY(d,2),FILTER(d,(w=1)+(w=3)))
