How to Skip Weekends and Generate only Working Days in Excel?

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

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:

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:

Our final function generates only working days as follows:

Check our step-by-step explainer video on how to generate only working days:

Leave a Reply

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

Join our weekly newsletter today!

As a thank you, we send a free Column Chart Guide to your inbox.