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")