You would need to find the working days in a given month in scenarios when you are processing the payroll or specifying the working days of a given month when checking the percentage of attendance of an employee and other such scenarios.
The NETWORKDAYS function can be used to calculate the number of working days in a given month. It automatically excludes the weekends. You can also specify the particular holidays and then count the business days.
Syntax
NETWORKDAYS(start_date, end_date, [holidays])
The function uses the following arguments:
- Start_date (required argument) – The Start date for the month
- End_date (required argument) – The End date for the month.
- Holidays (optional argument) – This specifies the list of holidays that should be excluded from the working days calculation.
Formula
If you have String Date
NETWORKDAYS(STRTODATE(“2020/07/01”,“YYYY/MM/DD”),STRTODATE(“2020/07/31”,“YYYY/MM/DD”))
To get it from current date
NETWORKDAYS(DATE(DATESTR(TODAY(),“YYYY”),DATESTR(TODAY(),“MM”),01),DATE(DATESTR(TODAY(),“YYYY”),DATESTR(TODAY(),“MM”),DAY(ROUND(EOMONTH(TODAY(),0),0))))
OR
NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),01),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(ROUND(EOMONTH(TODAY(),0),0))))
The ROUND function is required for rounding the serial number in decimals to the closest integer
Note :
- The NETWORKDAYS function includes both the start date and end date while calculating.
- An error is shown if the date given in the argument is not a valid date. Hence in the example, above we are using the DATE() function.
- In the example above, the start date and end date can be values from controls if required.