The following article introduces you to the NETWORKDAYS function - one of the functions in the date and time group function very popular in Excel.
Description: The function returns the number of whole working days between two dates by using a parameter that determines the number of weekends and which day of the week.
Syntax: NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])
Inside:
- start_date: Start date to calculate the number of working days, is a required parameter.
- end_date: The end date to calculate the number of working days, which is a required parameter.
- weekend: The method of calculating the number of weekends is not counted in the working day, including the values:
+ weekend = 1 or skip -> Weekends are Saturdays and Sundays.
+ weekend = 2 -> The weekend is Sunday, Monday.
+ weekend = 3 -> Weekends are Monday and Tuesday.
+ weekend = 4 -> Weekends are Tuesday and Wednesday.
+ weekend = 5 -> Weekends are Wednesdays and Thursdays.
+ weekend = 6 -> Weekends are Thursdays and Fridays.
+ weekend = 7 -> Weekends are Fridays and Saturdays.
+ weekend = 11 -> Sunday weekends.
+ weekend = 12 -> The weekend is Monday.
+ weekend = 13 -> The weekend is Tuesday.
+ weekend = 14 -> Weekend is Wednesday.
+ weekend = 15 -> The weekend is Thursday.
+ weekend = 16 -> The weekend is Friday.
+ weekend = 17 -> Weekends are Saturdays.
- holidays: Holidays need to be excluded from the working day, these days are usually not on a fixed list, is an optional parameter.
Attention:
- If start_date > end_date -> The return value is negative and the magnitude of the value will be the number of complete working days.
- If the value of start_date or end_date is outside the current date range -> the function returns the #NUM! Error value
- If the weekend value is not valid -> the function returns the #VALUE! Error value
For example:
Calculate the number of whole working days to know the number of weekends not counted in working days.
In the cell to calculate enter the formula: = NETWORKDAYS.INTL (E7, F7, G7, H7).
- Press Enter -> return value is:
Here the total number of working days is 240 days except Saturday and Sunday not working and the total has 40 Sundays and 40 Saturdays.
- Similarly, there is an extra day off -> number of working days is:
- Copy the formula for the remaining values to get the result:
- Where start_date > end_date -> return value is less than 0 -> actual number of working days in the past.
Here the value is -270 -> past working days is 270 days.
Above are instructions and some specific examples when using NETWORKWEEKDAYS.INTL function in Excel.
Good luck!