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

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we can do a wide range of work. One of them is to calculate working days in a month. In this article, I will show you how Excel calculates working days in a month. I will introduce four easy methods. Moreover, I will add the necessary images for your better understanding. Hopefully, you will get interesting vibes while using these four methods. Additionally, this will add extra efficiency to your Excel skills. So, follow the following steps to be familiar with the methods.


How to Calculate Working Days in a Month in Excel: 4 Effective Ways

In this article, I will use a dataset just like the one given below. In this dataset, it has four columns. The first portion of the dataset has two columns B and C called Month and Start Date. The second portion is the holidays covering two columns E and F. I will use this dataset throughout the whole article. By using this dataset I will show you how to calculate working days in a month.

Dataset of excel calculate working days in a month


1. Calculate Working Days in a Month in Excel by Introducing NETWORKDAYS Function

This is the first method of this article. I will show you the procedures to calculate working days in Excel by introducing the NETWORKDAYS function. Additionally, I have added a new column here called End Date. Follow the procedures step-by-step. I will show the necessary images also with the steps.

Calculating Working Days in a Month in Excel by Introducing NETWORKDAYS Function

Steps:

  • First, select the E5 cell of the dataset.

Calculating Working Days in a Month in Excel by Introducing NETWORKDAYS Function

  • Then, copy the following formula in the E5
=NETWORKDAYS(C5,D5,$G$5:$G$8)

Here,

  • =NETWORKDAYS(C5, D5,$G$5:$G$8): Returns the number of days from a given date to today without holidays.

Calculating Working Days in a Month in Excel by Introducing NETWORKDAYS Function

  • After pressing the Enter button, you find the result 20.
  • Then, use the Fill Handle to AutoFill the lower cells from F5 to F10.

Calculating Working Days in a Month in Excel by Introducing NETWORKDAYS Function

  • Consequently, you will find the following result.

Calculating Working Days in a Month in Excel by Introducing NETWORKDAYS Function

In this way, you will get the working days using the NETWORKDAY function.


2. Combining NETWORKDAYS and EOMONTH Functions

This is the second method of this article. I will show you here the procedure for calculating Working Days in a month by combining the NETWORKDAYS and EOMONTH functions. Hopefully, you will enjoy the whole procedure.

Steps:

  • Select the D5 cell first.

Combining NETWORKDAYS and EOMONTH Functions to excel calculate working days in a month

  • Write down the following formula in the selected cell.
=NETWORKDAYS(C5, EOMONTH(C5,0),$F$5:$F$8)

Here,

  • EOMONTH(C5,0): Returns the last date of a month mentioned in the C5 cell.
  • NETWORKDAYS(C5, EOMONTH(C5,0),$F$5:$F$8): Returns the value of networking days of a month without the holidays.

  • Then, press Enter and copy down the formula from D5 to D10.

Combining NETWORKDAYS and EOMONTH Functions to excel calculate working days in a month

  • As a consequence, you will find the result just like the picture given below.

Read More: How to Calculate Working Days between Two Dates in Excel


3. Applying TODAY Function to Calculate Working Days

This is the third method of this article. The method is quite different from the others. The procedure of this method is like the second method. But the problem is here I will not show the working days of a month rather than working days till today using the TODAY function. Follow the steps one by one.

Steps:

  • First, select the D5 cell.

Applying TODAY Function to Calculate Working Days in Excel

  • Then, write down the following formula in the selected cell.
=NETWORKDAYS(C5, TODAY(), $F$5:$F$8)

Here,

  • TODAY(): Returns today’s date. This value is used as the end date of the NETWORKDAYS function.
  • NETWORKDAYS(C5, TODAY(), $F$5:$F$8): Returns the number of days from a given date to today without holidays.

  • Then press Enter.
  • As a result, you will find the following result.

  • After copying down the formula, you will find the following result.

In this way, you will get the working days by applying the TODAY Function.


4. Using WORKDAY Function to Calculate Working Days in a Month in Excel

This is the last method of this article. This method is quite different from the others. I will use the WORKDAY function here. The function will not return the working day number whether it returns the last date of the working days of a period. Follow the procedure step by step.

Steps:

  • First, select the D5 cell.

Using WORKDAY Function to Calculate Working Days in a Month in Excel

  • Then copy the following formula in the selected cell.
=WORKDAY(C5, 30, $F$5:$F$8)

Here,

  • WORKDAY(C5, 30, $F$5:$F$8): Returns the last date of the working days of a period without holidays. Here, I took 30 as a timespan.

Using WORKDAY Function to Calculate Working Days in a Month in Excel

  • Then, after pressing enter, you will find the following result.
  • After that, copy down the formula.

Using WORKDAY Function to Calculate Working Days in a Month in Excel

  • As a result, you will find the following result like the picture given below.

In this way, you will get the last workdays by using the WORKDAY function.

Read More: How to Calculate Working Days in Excel Excluding Weekends and Holidays


Things to Remember

  • You should bear in mind that all the dates should be in Date format.

Download Practice Workbook

Please download the workbook to practice yourself.


Conclusion

In this article, I have tried to explain how Excel calculates working days in a month. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.


Related Articles

Souptik Roy
Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo