Indzara

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

Skip Weekends and Generate only Working Days START DATE GIVEN

Now, to begin, we’ll use the SEQUENCE function to generate 365 days

the syntax for which is:

Skip Weekends and Generate only Working Days SEQUENCE  function

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

Skip Weekends and Generate only Working Days WEEKDAY function

With a return type made as 2, the numeric equivalent of Saturday is 6 and Sunday is 7, as shown in a table below:

Skip Weekends and Generate only Working Days numeric equivalent

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

Skip Weekends and Generate only Working Days FILTER function

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:

Skip Weekends and Generate only Working Days

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 *