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 calculate working days in a month. I will introduce here 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 skill. So, follow the following steps to be familiar with the methods.
Download Practice Workbook
Please download the workbook to practice yourself.
4 Effective Ways to Calculate Working Days in a Month in Excel
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.
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.
Steps:
- First, select the E5 cell of the dataset.
- 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.
- After pressing the Enter button, you find the result 20.
- Then, use the Fill Handle to AutoFill the lower cells from F5 to F10.
- Consequently, you will find the following result.
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.
- 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.
- As a consequence, you will find the result just like the picture given below.
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.
- 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 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.
- 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 the holidays. Here, I took 30 as a timespan.
- Then, after pressing enter, you will find the following result.
- After that, copy down the formula.
- 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 workday function.
Things to Remember
- You should bear in mind that all the dates should be in Date format.
Conclusion
In this article, I have tried to explain Excel calculate 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. You will find such exciting blogs on our website Exceldemy.com. 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.
The post How to Calculate Working Days in a Month in Excel (4 Easy Ways) appeared first on ExcelDemy.
