You can calculate the total workdays per month in excel sheet by using a simple formula. This formula is based on the Excel EOMONTH and Excel NETWORKDAYS functions.
The general formula to find total workdays per month is as follows:
- Date argument is supplied as the start date of the month in cell reference or using Date function
- EOMONTH function is used as the argument to return end date of the same month.
- Holidays argument is optional, but it contains all the holiday’s dates in that year as a range of cells or named range to exclude those days from the calculation of workdays per month.
When the NETWORKDAYS function is applied, it calculates the number of working days per month or between a start and end point. It also considers holidays if there are any.
Excel EOMONTH function selects a date and returns the last date of the month. The list of holidays is provided as an argument in the NETWORKDAYS using name ranged “holidays” or as a range of cells.
For example, we have start dates of each month in column B that are formatted as month using the format “mmm”. While the ending date of each month is calculated by using the EOMONTH function as an argument in NETWORKDAYS function. For example, B5 contains Feb 1, 2016 but it only shows Feb in the month column as according to the “mmm” format.
Both the functions are used together to calculate the workdays per month as it can be seen in figure 1 below.
The formula used in C4 is as follows:
Where “holidays” in this example is named range that represents cell range E3:E13.
Figure 1: Workdays per month