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.

Overview of Calculating Workdays in a Month in Excel


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.
    Workdays by NETWORKDAYS Function

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

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


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 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.
    Calculating Workdays by EOMOMTH & NETWORKDAYS Functions

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.
    Calculating Workdays in a Month in the Current Year

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))

Calculating Workdays Based on Month and Year

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

Calculating Number of Workdays Based on Month and Year

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 DATEMONTH, 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.
    Calculating Last Workday

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

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


Download Practice Workbook

Please download the workbook to practice yourself.


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.


Frequently Asked Questions

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo