Applies to:
Crow Canyon NITRO activated sites in:
SharePoint Online and On-Premises 2013/2016/2019/SharePoint Server Subscription Edition

Description

This article has steps to calculate target date by excluding non-business days, as per site regional settings and holidays that are maintained in a custom list in the site.

In this example, it is required to calculate ‘Due Date’ in Tickets list by excluding weekends and holidays specified in ‘Holidays’ list.

Summary of steps

1. Create a custom list to maintain list of holidays.
2. Define work week in site regional settings.
3. Configure form event actions to calculate due date.

Detailed steps

1. Create a custom list to maintain list of holidays.

Go to ‘Site Contents’ -> Create a custom list. ‘Holidays’ is the list name in this example that is created for specifying holidays. It is required to create an item for each holiday in this list.

Create below columns in the list:

Column Name	Column Type
StartDate	Date and Time (Date only)
EndDate	Date and Time (Date only)

For continuous holidays, specify start date of holidays in ‘StartDate’ column and end date of holidays in ‘EndDate’ column.

For single holiday, specify ‘EndDate’ and leave ‘StartDate’ column blank.

Sample ‘Holidays’ list:

2. Define work week in site regional settings.

Go to site settings -> ‘Regional Settings’ under ‘Site Administration’ -> Define work week as required.

Sample work week:

3. Configure form event actions to calculate due date.

The projects form has a ‘Start Date’ column that will be entered by the user. User will also enter the number of days required to finish the project in ‘Days’ column. After that, system will automatically calculate the due date based on these two values.

• Navigate to NITRO Forms designer for Tickets list -> Create below columns and add them to the NITRO Form.

Column Name	Column Type
Start Date	Date and Time (Date & Time or Date only)
Due Date	Date and Time (Date & Time)
Days	Number (with ‘0’ decimal places)

• Configure form event actions.

Expand ‘Advanced’ section from left-hand panel -> Form Event Actions -> Configure FEA on column value change as shown below:

• Configure update form controls action to set due date.

• Add mapping for ‘Due Date’ column using custom script.

Script:

return window.ccs_g_FormUI.AddDaysFromNumberColumnToDateColumnValue("StartDate", "Days", true, true,"Holidays","EndDate", ["EndDate", "StartDate"], window.ccs_g_FormUI);

Parameters in the script	Description
StartDate	Internal name of ‘Start Date’ column in Tickets list. This is the source date column to calculate due date.
Days	Internal name of ‘Days’ column. Number of days that are to be added in ‘Start Date’ for calculating ‘Due Date’.
true	Consider business days defined in site regional settings. Possible values: true/false
true	Consider Holidays list while calculating ‘Due Date’. Possible values: true/false
Holidays	Custom list name that is created to maintain list of holidays.
EndDate	Internal name of ‘EndDate’ column in Holidays list.
StartDate	Internal name of ‘StartDate’ column in Holidays list.

Note: Replace column internal names (that are mentioned in the above table) with actual column internal names.

• Save settings.
• Configure update form controls action to clear ‘Due Date’ column if ‘Start Date’ or ‘Days’ column value is blank.

• Add mapping for ‘Due Date’ column.

• Save settings and publish the NITRO Forms.

Sample output