I am trying to create SLA calculator in a nintex form. The form should not save to a list rather it will it use runtime functions to calculate the expected date.
What I need is a calculator where you enter a date, and then it gives you an excepted completion date.
The calc must skip Weekends and holidays. The SLA takes 6 days to complete. So if I type in 7/1/2016 into the calc it will skip sat, sunday, 7/4(holiday), and 7/5 /(because 7/5 is technically the first business date after the submitted day so it is considered the "processing date.") from there it counts 7/6 as 1 , 7/7 as 2 , 7/8 as 3, skip sat and sun, 7/11 as 4, 7/12 as 5, and return 7/13.
I have created an excel sheet with a calendar starting with 1/1/2016 and going through 2018 as one column another column denotes Saturdays as 2(to remind me to add two days to get to Monday) and Sundays as 1. Holidays are also 1 because you only need to advance one day. I uploaded the list into sharepoint and then performed list lookups returning if its a sat or sun or holiday and if so I added the necessary days.
I cant get the formulas to skip over the holidays when they are in the week.
Below is me taking the add, and then determining if the day is a weekend or holiday if so I added a day. I was going to continue this process until the 6th day.
Its not working out im wondering if this is even possible.
add +1= formatDate(if(submittedDay=="Saturday",dateAddDays(submitted,2),if(submittedDay=="Sunday",dateAddDays(submitted,1),dateAddDays(submitted,1),dateAddDays(submitted,1)),dateAddDays(submitted,1)),"D")
submitted day= lookup("SLAHolidays","Date",Add,"Day")
From the research that I've done into this topic, Nintex is not yet able to exclude weekends and holidays when trying to calculate working days. I was trying to figure out how to do this as well, to be able to establish a due date 90 working days after the start date. The best I was able to do was to add a month onto the due date calculation, as a month is somewhat equivalent to the number of weekend days over a period of three months.
at first, if I correctly refactored your single line formula, IFs conditions are not syntactically correct - you have there too many options in there.
at second, you base the calculation how many days to add just on submit day.
however, within scope of the example you go over the weekend twice, which the formula doesn't consider.
have a look eg. here Calculate Business Date