To count the working days in a month, pair NETWORKDAYS with EOMONTH: =NETWORKDAYS(A1,EOMONTH(A1,0),A4:A14), where A1 is the first day of the month and A4:A14 is an optional list of holiday dates. EOMONTH with a 0 returns the last day of that month, so NETWORKDAYS counts from the first to the last, skipping Saturdays and Sundays plus anything in your holiday list.
One thing to watch: this only gives the full month if A1 is actually the 1st — point it at 15-Jan and you’ll get the working days remaining from the 15th onward. To make it work from any date in the month, use =NETWORKDAYS(EOMONTH(A1,-1)+1,EOMONTH(A1,0),A4:A14).
And if your working week isn’t Monday to Friday, NETWORKDAYS won’t help — use NETWORKDAYS.INTL instead: =NETWORKDAYS.INTL(A1,EOMONTH(A1,0),11,A4:A14) treats Sunday alone as the weekend, and 1 through 17 cover the other combinations, including a custom "0000011" string where each digit is a day from Monday.
To get the total number of business days (working days) in Excel using a formula, you need to combine two functions (EOMONTH and NETWORKDAYS). In this formula, EOMONTH helps you to get the last date of the month, and by using that last date and the first date specified by you, NETWORKDAYS returns the total number of business days in a month.
In this tutorial, we will learn to write this formula. And we will also consider holidays that you need to specify while calculating the total days.
Excel Formula to Get Total Business Days in a Month
You can use the below steps:
- First, in a cell, enter the NETWORKDAYS function.
- After that, in the first argument (start_date) refer to the cell where you have the starting date of the month.
- Next, in the second argument, (end_date) enter the EOMONTH function.
- From here, in the EOMONTH, specify the starting date of the month that you have referred to in the start_date argument of the NETWORKDAYS.
- Now, enter zeros (0) in the (months) argument of the EOMONTH, and type closing parentheses to close the function.
- In the end, refer to the list of holidays (this needs to be a list of valid dates) in the [holidays] argument of the NETWORKDAYS, type the closing parentheses, and hit enter to get the result.
=NETWORKDAYS(A1,EOMONTH(A1,0),A4:A14)
=NETWORKDAYS(start_date,EOMONTH(start_date,0),list_of_holidays)How Does This Formula Works?
As I mentioned earlier, the EOMONTH function returns the last date of the month. When you specify a date in the function, it returns the last date of that month.
NETWORKDAYS uses that date as an ending date to get the total number of days between these two dates.
And in the end, when you specify a list of dates that are holidays. NETWORKDAYS also considers that while calculating the total number of days.
In the example above, we have used the first day of January month of 2023. In January, we have 31 days in total. And when you check it manually, you have 10 weekends and holidays in the month. That means 21 working days for a business.
And that’s the exact number of business days we have got in the result.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.