Through this post, we’ll learn how to skip weekends and generate only working days, that is days except Saturdays and Sundays, given a starting date.
Consider, a starting date is given in cell K2 as shown
Now, to begin, we’ll use the SEQUENCE function to generate 365 days
=SEQUENCE(K3,1,K2,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.
Note that SEQUENCE function is available only with Excel 365 subscriptions or in Excel version 2021.
Before we go further, please note that by default, Excel considers Sun=1, Mon=2, and so on. But, this can be modified using the WEEKDAY function whose syntax is
With a return type made as 2, the numeric equivalent of Saturday is 6 and Sunday is 7, as shown in a table below:
We’ll use the FILTER function to get only the dates which are less than or equal to 5; thus eliminating all Saturdays and Sundays from the result. Our formula in cell K6 will be:
=FILTER(SEQUENCE(K3,1,K2,1),WEEKDAY(SEQUENCE(K3,1,K2,1),2)<=5)Syntax for FILTER is
In this scenario, the function returns the number of weekdays within the said 365 days. We can verify our results by using a TEXT function:
=TEXT(K6,"ddd")Our final function generates only working days as follows:
Check our step-by-step explainer video on how to generate only working days:
