How to Count the Number of Weekdays in a Month in Excel?

In this blog, from our Data to Decisions series, we focus on a key calculation that is widely used: counting the number of weekdays. This is particularly useful for project scheduling, resource allocation, capacity estimation, and much more.  

By using the SEQUENCE, WEEKDAY, and SUM functions in Excel this should take just a few seconds! Read to know how?

For a sample scenario, consider the a given year in cell C3 as shown:

To get the number of weekdays, we start by calculating the starting date, ending date, and number of days in each month as shown below in columns C, D, and E.  

Since we covered these steps in our previous blog posts, please read step-by-step instructions by clicking here: How to calculate the starting dates, and ending dates in a month. 

With the starting and ending dates, the number of days is acquired with a simple formula as shown:

=D6#-C6#+1

After we know the starting date and the number of days, we can proceed with the next step.  

Since Excel, by default considers Sun=1, Mon-2, and so on, for our calculations we will use the same as shown below from cell F4 to L4:

To get the number of weekdays, say in our example, let us begin with the number of Sundays, the overview of the steps are as follows:

1. Extract all the dates for each month.
2. Find the weekday (whether it’s a Sunday or Monday and so on?)
3. Count only the weekday from step 2.

Let’s get into the details of understanding each step:

Step 1:

Using the SEQUENCE function we will get the series of all days for each month.

In cell F6, we write the following formula.  

=SEQUENCE($E6,1,$C6,1)

The syntax for which is,

For January, this formula generates a sequence of 31 (cell E6) consecutive days from the start of the month (cell C6)

Step 2:

Continuing with the same formula, in cell F6, if we wrap the WEEKDAY function around it, we will get the weekday for all the days in January as arrived in step 1.  

=WEEKDAY(SEQUENCE( $E6,1,$C6,1))

The weekday function returns the day of a given week, with the following syntax

This will convert the dates into numeric values ranging from 1 (Sun) to 7 (Sat).

Step 3

Now, from this list, we need only the 1s (Sundays), 2s (for a Monday) and so on. This is easily done by equating the formula from the previous step to 1 (for Sun).

Since in our example, we are first focusing on getting the count of Sundays, instead of hardcoding the value to 1 (for Sun), we can point to the cell F4 where we have already entered 1.  

=WEEKDAY(SEQUENCE($E6,1,$C6,1))=F$4

Note: F4 should be entered as F$4, and I will explain the reason later below

This function returns TRUE for all Sundays and FALSE for all other weekdays.

To make this return 1/0 instead of TRUE/FALSE we need to add  — (dash dash)  in front of the formula, as shown below.

= --(WEEKDAY(SEQUENCE($E6,1,$C6,1))=F$4)

Step 4

Wrap the whole function inside Sum to get the count of all Sundays in January

= SUM(--(WEEKDAY(SEQUENCE($E6,1,$C6,1))=F$4))

Now that we have calculated the count of Sundays in January, how do we do it for all the months, and how do we calculate the count of other weekdays?

The answer is that we have already done the necessary calculations. We must just extend the formula to all the cells (all weekdays). You can just click and drag the formula to all the cells.  

Voila! You have the count of all weekdays in a month, for all the months as shown below,

How? 

All this simplicity was possible due to the relative references concept built within Excel. This is the reason why F4 was entered as F$4 in the formula. We lock the reference to the 4^th row where we have entered the numeric equivalents of each weekday. This allows one formula to be applied to all the cells easily.  

Check our detailed video on counting number of weekdays on our YouTube channel,