How to Calculate the Working Days in a Month in Excel

How to Calculate the Working Days in a Month in Excel – 3 Methods

Written by Souptik Roy

Reviewed by Mursalin Ibne Salehin

Last updated: Aug 3, 2024

In the table below a formula was used to return the working days, minding public holidays.

Method 1 – How to Calculate Working Days in a Month in Excel

Using NETWORKDAYS Function

STEPS

Select a cell to store the number of working days in a month. and enter =NETWORKDAYS(.
Enter the start_date or its cell reference in the first argument.
Insert the end_date and range of [holidays] in the second and third arguments.
Close the parentheses and press Enter.
The formula will return the number of working days from start_date to end_date.

Apply the Fill Handle to autofill the other cells.

Using the NETWORKDAYS and EOMONTH Functions

STEPS

Select a cell to store the working days in a month and enter =NETWORKDAYS(.
Enter the start_date or its cell reference in the first argument.
Insert the end_date argument using the EOMONTH function.
- Write the start_date or its cell reference in the first argument.
- Type 0 in the months argument.
Provide the range of [holidays] in the final argument of the NETWORKDAYS function.
Close the parentheses and press Enter.
The formula returns the number of working days in the month of January.
Use the Fill Handle to autofill the other cells.

Using the DATEVALUE, NETWORKDAYS, and EOMONTH Functions

STEPS

Select a cell and enter the formula: =NETWORKDAYS(DATEVALUE(B5&"-1"),EOMONTH(DATEVALUE(B5&"-1"),0),$E$5:$E$8)
Use the Fill Handle to autofill the other cells.
The formula returns the number of working days using the names of the months.

Method 2 – How to Calculate Working Days Based on Month and Year in Excel

This is the formula.

=IF(MONTH(WORKDAY(DATE($F$5,MONTH(DATEVALUE($F$4&" 1")),0),$B5))<>MONTH(DATEVALUE($F$4&" 1")),"",WORKDAY(DATE($F$5,MONTH(DATEVALUE($F$4&" 1")),0),$B5))