Excel Guides

How to Calculate the Number of Weekdays in a Month in Excel

To calculate the number of weekdays in a month in Excel, you can use a simple formula based on the DAY function. The DAY function returns the day of the month for a given date, and by using it in conjunction with the WEEKDAY function, we can get a count of the number of weekdays in a month.

In the example shown, the formula in cell C5 is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&EOMONTH(B5,0))))<6))

This formula returns the value 28, which is the number of weekdays in February, 2020.

How this formula works

At its core, this formula uses the INDIRECT function to create an array of dates like this:

[1-Feb-2020, 2-Feb-2020, ... 28-Feb-2020]

The array is created by concatenating 1 (the start day) with the result of the EOMONTH function (the end day). This array is fed into the ROW function, which returns an array of row numbers like this:

[1, 2, ... 28]  // row numbers corresponding to dates in array above.  

The ROW/INDIRECT part of the formula creates an array that looks like this:

[WEEKDAY(1), WEEKDAY(2), ... WEEKDAY(28)] // weekday numbers for each day in month  

(The WEEKDAY function returns a number between 1 and 7 that represents each day of the week.)

(Note: if you're not familiar with using arrays in formulas, don't worry. You don't need to know how they work to use this formula. Just know that when you see an array like this [WEEKDAY(1), WEEKDAY(2), ... WEEKDAY(28)], it means that Excel is evaluating the WEEKDAY function for each value in the array.)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.