Auto Populating Due Date on the SharePoint/Infopath form to 5 business Days

This is done after the SharePoint list is customized using InfoPath.

Follow the steps below to prepopulate the Date & Time field to 5 business days from today.

- Add a text field and remove it to appear on the form.
- Under Data tab add the rule below to set the textbox with below formula.

(number(substring(today(), 9, 2)) + number(number(substring(today(), 1, 4)) - floor((14 - number(substring(today(), 6, 2))) / 12)) + floor(number(number(substring(today(), 1, 4)) - floor((14 - number(substring(today(), 6, 2))) / 12)) / 4) - floor(number(number(substring(today(), 1, 4)) - floor((14 - number(substring(today(), 6, 2))) / 12)) / 100) + floor(number(number(substring(today(), 1, 4)) - floor((14 - number(substring(today(), 6, 2))) / 12)) / 400) + floor(31 * number(number(substring(today(), 6, 2)) + 12 * floor((14 - number(substring(today(), 6, 2))) / 12) - 2) / 12)) mod 7 + 1

NOTE: This not my formula and I lost the URL where I found this. Please if anyone knows it, please cite it here.

- Above formula returns 1 if Sunday, 2 if Monday and so on. So in my case to add 5 business days, I added 3 rules, one for weekday, one for Sunday and one for Saturday.
1. Weekday Rule: textbox = 2 or textbox =3 or textbox =4 or textbox = 5 or textbox =6
set due date = adddays(now(),7).
Check Don’t run remaining rules. This is very important
2. Sunday Rule: textbox = 1
set due date = adddays(now(),5)
Check don’t run remaining rules
3. Saturday Rule: textbox = 7
set due date = adddays(now(),6)
Check don’t run remaining rules

All the above rules are calculated on form load.

Create views based on workflow status

The workflow Status values will be stored as numbers instead of string values. So to create a view to see all the "Completed" workflow tasks, go to modify the view and apply the filter as shown below

For other status follow the below table

Status	Value
	Not Started	0
	Failed on Start	1
	In Progress	2
	Error Occurred	3
	Canceled	4
	Completed	5
	Failed on Start (retrying)	6
	Error Occurred (retrying)	7
	Canceled	15	* This is defined but I don’t think this value is used
	Approved	16
	Rejected	17

Adding more than 5 fields in InfoPath Rules

It looks like InfoPath doesn’t allow more than 5 fields in a rule with “And” being greyed out after that. But you can add as many (at least I have added 35 fields) in a rule with below approach. In my scenario all my fields were named with numbers. But it works the same with any type of field.
Below are the instructions.

- Add the 5 fields(including 5th).
- On the last field, change it to “The expression". Copy the expression and type “and” or “or” and paste it.
- Now change the name of the field and so on add all the fields.

Setting Due Date (in business days) inside the workflow

We had a requirement to assign a task to the user but the due date has to be set to 5 business day (Just excludes Saturday & Sunday. Other company holidays are not considered) from the day task will be assigned. So the below code was added in "Before a Task is assigned" section in approval workflow.


The first line is self explanatory. In Second line copy the CurrentDate (long date) to a string variable called Variable:StringCurrentDate. Now the value of the string will of the format Thursday, April 07, 2016. Using copy function copied the first 3 characters which will be Mon, Tue, Wed etc. If the value is Mon, Tue.... Friday add 7 days, if Sun add 5 and if Sat add 6.


Then use the variable:date to set the task:due date.