The networkdays function returns
the number of working days (Monday-Friday) between and including start_date and end_date
taking into account any optionally listed holiday.
Syntax:
networkdays
(start_date, end_date [, holiday])
Return data type: integer
Calendar diagram displaying date range returned by networkdays function
The networkdays function has the following limitations:
There is no method to modify workdays. In other words, there is no way to modify the function for regions or situations that involve anything other than working Monday to Friday.
The holiday parameter must be a string constant. Expressions are not accepted.
Arguments
Argument
Description
start_date
The start date to evaluate.
end_date
The end date to evaluate.
holiday
Holiday periods to exclude from working days. A holiday is stated as a string constant date. You can specify multiple holiday dates, separated by commas.
The networkdays() function is commonly used as part of an expression when the user would like the calculation to use the number of working week days that occur between two dates. For example, if a user would like to calculate the total wages that will be earned by an employee on a PAYE (pay-as-you-earn) contract.
Function examples
Example
Result
networkdays ('12/19/2013', '01/07/2014')
Returns
14. This example does not take holidays into account.
Returns 10. This example takes two holiday periods into account.
Regional settings
Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples. For more information, see Modifying regional settings for apps and scripts.
Default regional settings in apps are based on the user profile. These regional format settings are not related to the language displayed in the Qlik Cloud user interface. Qlik Cloud will be displayed in the same language as the browser you are using.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
id
start_date
end_date
net_work_days
Results table
id
start_date
end_date
net_work_days
1
01/01/2022
01/18/2022
12
2
02/10/2022
02/17/2022
6
3
05/17/2022
07/05/2022
36
4
06/01/2022
06/12/2022
8
5
08/10/2022
08/26/2022
13
Because there are no scheduled holidays (this would have been present in the third argument of the networkdays() function), the function subtracts the start_date from the end_date, as well as all weekends, to calculate the number of working days between the two dates.
Calendar diagram highlighting work days for project 5 (no holidays)
The calendar above visually outlines the project with id of 5. Project 5 begins on Wednesday, August 10, 2022 and ends on August 26, 2022. With all Saturdays and Sundays ignored, there are 13 working days between, and including, these two dates.
Example 2 – Single holiday
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
The same dataset and scenario from the previous example.
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.
The creation of an additional field, net_work_days, to calculate the number of working days involved in each project.
In this example, there is a one-day holiday scheduled on August 19, 2022.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
id
start_date
end_date
net_work_days
Results table
id
start_date
end_date
net_work_days
1
01/01/2022
01/18/2022
12
2
02/10/2022
02/17/2022
6
3
05/17/2022
07/05/2022
36
4
06/01/2022
06/12/2022
8
5
08/10/2022
08/26/2022
12
The single scheduled holiday is entered as the third argument in the networkdays() function.
Calendar diagram highlighting work days for project 5 (single holiday)
The calendar above visually outlines project 5, demonstrating this adjustment to include the holiday. This holiday occurs during project 5 on Friday, August 19, 2022. As a result, the total net_work_days value for project 5 decreases by one day, from 13 to 12 days.
Example 3 – Multiple holidays
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
The same dataset and scenario from the first example.
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.
The creation of an additional field, net_work_days, to calculate the number of working days involved in each project.
However, in this example, there are four holidays scheduled from August 18 to August 21, 2022.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
id
start_date
end_date
net_work_days
Results table
id
start_date
end_date
net_work_days
1
01/01/2022
01/18/2022
12
2
02/10/2022
02/17/2022
6
3
05/17/2022
07/05/2022
36
4
06/01/2022
06/12/2022
8
5
08/10/2022
08/26/2022
11
The four scheduled holidays are entered as a comma separated list, from the third argument onwards in the networkdays() function.
Calendar diagram highlighting work days for project 5 (multiple holidays)
The calendar above visually outlines project 5, demonstrating this adjustment to include these holidays. This period of scheduled holidays occurs during project 5, with two of the days occurring on a Thursday and Friday. As a result, the total net_work_days value for project 5 decreases from 13 to 11 days.
Example 4 – Single holiday
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
The same dataset and scenario from the first example.
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.
There is a one-day holiday scheduled on August 19, 2022.
However, in this example, the unchanged dataset is loaded into the application. The net_work_days field is calculated as a measure in a chart object.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
id
start_date
end_date
Create the following measure:
= networkdays(start_date,end_date,’08/19/2022’)
Results table
id
start_date
end_date
net_work_days
1
01/01/2022
01/18/2022
12
2
02/10/2022
02/17/2022
6
3
05/17/2022
07/05/2022
36
4
06/01/2022
06/12/2022
8
5
08/10/2022
08/26/2022
12
The single scheduled holiday is entered as the third argument in the networkdays() function.
Calendar diagram showing net work days with single holiday (chart object)
The calendar above visually outlines project 5, demonstrating this adjustment to include the holiday. This holiday occurs during project 5 on Friday, August 19, 2022. As a result, the total net_work_days value for project 5 decreases by one day, from 13 to 12 days.
Dataset
The term dataset is sometimes synonymous with table. It can refer to the original source table, the table after undergoing transformations, or the fact and dimension tables in a data mart.
It can also refer to a logical table, where there are several instance tables and views:
Current data
History, which holds previous versions of the table
A field contains values, loaded from a data source. At a basic level, a field corresponds to a column in a table. Fields are used to create dimensions and measures in visualizations.
A measure is a calculation base on one ore more aggregations. For example, the sum of sales is a single aggregation, while the sum of sales divided by the count of customers is a measure based on two aggregations.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.