How to Calculate Working Days in a Month in Excel (3 Ways)

Calculating working days in Excel is often essential for businesses and individuals when planning and tracking tasks. It also helps in determining project timelines, employee work hours, and other time-sensitive activities by excluding non-working days such as weekends and holidays.

In this Excel tutorial, you will learn how to calculate working days in a month. We are going to use different functions for this purpose.

Say, you want to find out the number of working days in the first six months in a year. The public holidays are also included in those months. These public holidays are celebrated in the USA. The formula used here returns the working days keeping these public holidays into calculation.

3 Ways to Calculate Working Days in a Month in Excel

The working days in a month or in a range of days can be calculated by Excel formulas. You can see that we used different functions in the following sections. Each formula has corresponding advantages and its use cases. Here you can find 3 easy ways to calculate working days in a month:

Using NETWORKDAYS Function

If the dataset contains a beginning date and an end date, the NETWORKDAYS function can be useful. The functionÂ has three arguments: start_date, end_date, and [holidays]. It calculates days between two dates. It automatically removes the weekends from the date range. Adding other holidays in the formula also excludes them from the calculation. Thus we can determine workdays in the corresponding date range.

To calculate working days in a month using the NETWORKDAYS function, follow the steps below:

1. Select a cell to store the number of working days in a month.
2. Write =NETWORKDAYS( in it.
3. Type theÂ start_dateÂ or its cell reference in the first argument.
4. Similarly, insert theÂ end_dateÂ and range of [holidays]Â in the second and third argument respectively.
5. Close the parentheses and pressÂ Enter.
The formula will return the number of working days fromÂ start_date toÂ end_date.

Using the data of our worksheet, the formula becomes: =NETWORKDAYS(C5,D5,\$G\$5:\$G\$8)

Apply the Fill Handle to autofill the lower cells if your dataset contains multiple months.

Here, cell C5 = Start Date, D5 = End Date, and \$G\$5:\$G\$8 = the range of Holidays. There are 31 days in January. You can also see that there are two public holidays in this month. The formula may vary based on your cell reference.

The formula for calculating working days in January becomes:

= 31 – No of weekly holidays – No of public holidays + No of public holidays that are on weekly holidays

Using NETWORKDAYS and EOMONTH Functions

Using the EOMONTH function with the NETWORKDAYS function does not require an additional helping column for the end days of a month. TheÂ EOMONTHÂ function has two arguments:Â start_date andÂ months.

To calculate working days in a month using NETWORKDAYS and EOMONTH functions, follow the steps below:

1. Select a cell to store the working days in a month.
2. Write =NETWORKDAYS( in it.
3. Type theÂ start_dateÂ or its cell reference in the first argument.
4. Insert 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.
5. Provide the range of [holidays]Â in the final argument of theÂ NETWORKDAYSÂ function.
6. 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 lower cells if your dataset contains multiple months. Using the data of this worksheet, the formula becomes: =NETWORKDAYS(C5, EOMONTH(C5,0),\$F\$5:\$F\$8)

You can see all the working days of the first six months after this formula.

Using DATEVALUE, NETWORKDAYS, and EOMONTH Functions

CombiningÂ DATEVALUE, NETWORKDAYS,Â and EOMONTH functions is helpful if you want to calculate the workdays based on the month names in the current year. You don’t need to use start and end dates in this case.

To calculate working days based on month names, follow the steps below:

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

The formula works similarly to the previous one. Here, it creates the start_dateÂ argument using the month name with the help of theÂ DATEVALUEÂ function.

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

If you want to calculate the workdays based on month and year, you can first develop a formula to make a list of working days in a month of a particular year. Then you can use theÂ COUNTÂ function to calculate the number of working days.

The formula to generate the working days based on month and year is given below: =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))

The formula to count the number of working days is: =COUNT(C5:C27)

C5:C27Â refers to the date range of working days. In the image above, the dates are created based on the corresponding month and year using DATE,Â MONTH, DATEVALUE, and IF functions.

Note: Here, the date range of public holidays was not used. So you will get the workdays excluding public holidays.

How to Calculate Last Workday in a Month in Excel

Sometimes employers need to find out the last workday in a month. The WORKDAY function can be helpful in this case.

To calculate the last workday using the WORKDAY function, follow the steps below:

1. Select a cell and insert the formula: =WORKDAY(EOMONTH(C5,0)+1,-1, \$F\$5:\$F\$8)
2. Use the Fill Handle to autofill the lower cells.

The formula returns the last workdays keeping the holidays in calculation.

Conclusion

To sum up, you can find easy and efficient solutions for calculating the working days in Excel after going through this article. We have used different formulas for this purpose. However, the NETWORKDAYS function was common in all methods. This is the key function to calculate the working days in a month or in a range of days. If you have any queries, feel free to ask me in the comment section. Donâ€™t forget to give us your feedback.

How to calculate workdays till today in a month?

To calculate all the working days till today using the TODAY function, follow the steps below:

1. Select a cell to store the workdays and insert the formula: =NETWORKDAYS(start_date,TODAY(),[holidays])
2. If you have more cells, use the Fill Handle to autofill the lower cells.

What is the formula for calculating days in Excel?

To calculate days between two dates, you can use the DAYS function. Here is the formula structure: =DAYS(end_date,start_date)

How to count workdays between two dates including Saturday in Excel?

To count workdays between two dates including Saturday, use the formula below in a cell:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(start_date & ":" & end_date)),"dddd")<>"Sunday"))

Related Articles

<< Go Back to Excel WORKDAY FunctionÂ | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF