NETWORKDAYS Function in Excel
The NETWORKDAYS function calculates the number of working days between two given dates. It is a date and time function that is often used in accounting and finance. NETWORKDAYS automatically excludes the weekend (Saturday and Sunday) from the working days. While calculating the workdays, it is mandatory to enter a start date and an end date. However, it is optional to provide the dates of the holidays.
The syntax of the NETWORKDAYS function is “=NETWORKDAYS(Start Date, End Date, Holidays).”
The NETWORKDAYS function accepts the following arguments:
- Start date – This is the start date that you wish to use in the calculation of working days. This value is always entered as a serial date and not as a text date.
- End date – This is the end date that you wish to use in the calculation of working days. This value is also entered as a serial date and not as a text date.
- Holidays – This represents the list of holidays that are excluded from the workday calculation. This can be entered either as a range of cells containing holiday dates (F2:F5)
or as a list of serial numbers that represent the holiday dates.
The return value is a numeric value representing days.
How to Use the NETWORKDAYS Function in Excel?
Let us consider the following examples to understand the working of the NETWORKDAYS function.
The following image contains serial dates in columns A, B, and F. To view the numeric value of the serial dates, the cell formatCell FormatFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user's preferred format. The formatting of the cell depends upon the nature of the data present. can be changed to “General.”
The optional parameter of holidays is entered as a range of cells, i.e., F2:F5. We apply the NETWORKDAYS formula “=NETWORKDAYS(A2, B2, F2:F5).”
The output is 3, as shown in the succeeding image.
Note: To enter holidays as text dates, follow the regional date and time settings of Excel.
Let us apply “=NETWORKDAYS(A3, B3, F2:F5).”
The output is 5.
Here we apply the formula “=NETWORKDAYS(A4, B4, F2:F5).”
The number of workdays we get is 4.
Let us use the formula “=NETWORKDAYS(A5, B5, F2:F5).”
The output is 9.
The outcome of the four examples is summarized in the following image.
Functions of NETWORKDAYS in Excel
- It calculates the total number of working days (business days) between two given dates.
- It automatically excludes the weekend (Saturday and Sunday) and also allows omitting holidays.
- It calculates the whole workdays and ignores any time values.
- It brings more flexibility to the system when used with the NETWORKDAYS.INTL function.
The Applications of NETWORKDAYS Function
The NETWORKDAYS function can be used for various purposes. For instance, this function helps calculate employee benefits based on workdays, the total days required to complete a project, the workdays required to resolve a customer support issue, and so on.
Some applications of the NETWORKDAYS formula in Excel spreadsheets are listed as follows:
- Counts the total number of business days
- Calculates workdays per month
- Counts the number of working days left in a month
- Calculates the work hours between dates
The Output of NETWORKDAYS Function
The NETWORKDAYS function requires a start date and an end date to work. This function returns the following values:
- If the start date precedes the end date, the function returns a positive value.
- If the start date is the same as the end date, the function returns the value 1.
- If the end date precedes the start date, the function returns a negative value.
- If any of the arguments are not recognized by Excel as valid dates, the function returns a #VALUE! error#VALUE! Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error..
Frequently Asked Questions
The NETWORKDAYS.INTL function is similar to the NETWORKDAYS function. The major difference between the two functions is that the former allows specifying the dates that would be considered weekends.
The syntax of NETWORKDAYS.INTL function is:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
The “weekend” is an optional argument that accepts either a number or a string. For instance, the number “2” is allotted to Sunday and Monday, “3” is allotted to Monday and Tuesday, and so on. By default, NETWORKDAYS.INTL function considers Saturday and Sunday as a weekend.
A string is a series of values that contains seven characters. Each character stands for a day of the week, with Monday being the first day of the string. The string contains only “0” and “1” as characters, with “0” being a workday and “1” being a non-workday. For instance, the string “1000001” implies that Monday and Sunday are a weekend.
The differences between the two functions are listed as follows:
– The WORKDAY functionWORKDAY FunctionThe WORKDAY function returns the forthcoming work day after a given number of days from the start date. It has an optional argument for holidays that, if not provided, automatically considers weekends (Saturdays and Sundays) to be holidays. is used to calculate a date that is “N” days in the past or the future from a given date. On the other hand, the NETWORKDAYS function is used to determine the number of workdays between two specified dates.
– The output of the WORKDAY function is a date, while the output of the NETWORKDAYS function is a numeric value.
– The WORKDAY function is used to create the timeline of a project, determine the milestones of an organization, and so on. In contrast, the NETWORKDAYS function is used to plan deadlines for a project, determine the time required to fix an issue, and so on.
The working hours can be calculated with the help of the following formula:
NETWORKDAYS(start_date, end_date, holidays)*hours
The “hours” stand for the number of working hours on a working day.
Note: It is assumed that the number of working hours on all working days is the same. In case you wish to specify the weekend, use the NETWORKDAYS.INTL formula.
- The NETWORKDAYS function calculates the total number of working days between two dates.
- The weekend (Saturday and Sunday) is automatically excluded from the NETWORKDAYS in Excel.
- The holidays can be omitted from the total workdays at the option of the Excel user.
- The NETWORKDAYS formula in Excel calculates the whole workdays and ignores time values.
- The workdays can be calculated only if the start and end dates are provided.
- The numeric value of serial dates can be viewed if the cell format is changed to “General.”
This has been a guide to NETWORKDAYS in Excel. Here we discuss how to use NETWORKDAYS Formula in Excel along with Excel examples and downloadable templates. You may also look at these useful functions in Excel –