Calculating Business Days

C
Badge +3

Hello, 

I have workflow that requires the calculation of business days from the time the form is submitted to an estimated delivery/completion date. Currently I am using Dox42's UDA. The issue is, this workflow will calculate out 10 days ahead but will not go any further. I need the tool to calculate up to 30 business days out. I have attempted to use the current UDA from Dox but cannot get it to calculate the 30 day requirement. Does anyone have any thoughts on this or have some other UDA that would work?

dox42 Workflow Utils 

Thank you, 


Cody Allison

9 replies

M
Userlevel 3
Badge +9

If you go into the UDA, it's designed to only look up to a max of 10 days ahead.  It does this by creating a collection of 10 dates.  If you want to change it to go up to 30 days, modify the process in the "Init loop collection" action set by copying the 2 repeating actions another 20 times. 

202803_pastedImage_1.png

Personally, I use a solution that Ryan Greenaway‌ mentions here Calculating a due date when i have "lag days" - Workflow Question which uses a spreadsheet to calculate the days and Excel Services to input the variables and retrieve the results. 

C
Badge +3

Murph, 

That was the first thing I tried to do but it could not accurately calculate the date. It made the workflow error if you tried to calculate out past 10 days. 

M
Userlevel 3
Badge +9

What's the error?  There must be some other configuration you need to change as well.  I don't use that UDA, but rather an Excel spreadsheet to do the calculations.  It will handle anything. 

C
Badge +3

The error I receive is telling me that it cannot calculate the value in the colum and tout in a fake date of 12/31/1899 as the value.

Thank you,

Cody Allison

Business Systems Analyst

PH: (219) 861-5277<tel:(219)%20861-5277>

CL: (219) 262-4913<tel:(219)%20262-4913>

Hours: MTWTF 8:00am to 4:30pm<x-apple-data-detectors://1/>

IT Project request

http://workflows.accudyneindustries.com/requests/Sullair/SitePages/IT Request Summary.aspx<http://workflows.accudyneindustries.com/requests/Sullair/SitePages/IT%20Request%20Summary.aspx>

IT Intranet Page

https://accudyneindustries.sharepoint.com/sites/SU-IT/SitePages/Home.aspx

Telecommunications Intranet Page

https://accudyneindustries.sharepoint.com/sites/SU-IT/telecom/SitePages/Home.aspx

P
Userlevel 4
Badge +12

I don't use that action either but it might be helpful to share some screenshots of your actions configuration and the variables you're using in it.

Kind regards,

Enrico

C
Badge +3

Enrico, 

Please see the images I have attached for the Variables, Parameters, and the structure. I have modified the Init Loop Collection to 30 inputs as recommended above and I am still receiving the error. The image showing the date is what happens when you try to calculate anything past the 10 day parameter this was originally built with. It shows a date of 12/31/1899. Any recommendation would be appreciated. Thanks!

 BuildVariablesParametersTarget Completion Date

M
Userlevel 3
Badge +9
  1. Create a spreadsheet with 3 columns.  Start Date, Number Business Days, End Date.
    202963_pastedImage_2.png
  2. Either on the same sheet, or a new sheet, create an Excel table with a list of your holidays.
    202967_pastedImage_3.png
  3. In the End Date column set it to the formula =WORKDAY(A2,B2,Table1[Holidays]).
  4. Save your spreadsheet to your site.  I put mine in the Site Assets library.  Just make sure whatever credentials you use for the Query Excel Services, that it will have access to the workbook. 
  5. Configure a Query Excel Services action as follows.  You will be updating the Start Date and the Number of Days, then retrieving the End Date. 
    202969_pastedImage_5.png
  6. You can now update the end date where needed.
M
Userlevel 3
Badge +9

‌, did you try this option out and did it work for you?

C
Badge +3

This did work correctly. However, our environment does not have excel services enabled. So I did get it to function correctly but I could not use it in the workflow, unfortunately.

Thank you,

Cody Allison

Business Systems Analyst

PH: (219) 861-5277<tel:(219)%20861-5277>

CL: (219) 262-4913<tel:(219)%20262-4913>

Hours: MTWTF 8:00am to 4:30pm<x-apple-data-detectors://1/>

IT Project request

http://workflows.accudyneindustries.com/requests/Sullair/SitePages/IT Request Summary.aspx<http://workflows.accudyneindustries.com/requests/Sullair/SitePages/IT%20Request%20Summary.aspx>

IT Intranet Page

https://accudyneindustries.sharepoint.com/sites/SU-IT/SitePages/Home.aspx

Telecommunications Intranet Page

https://accudyneindustries.sharepoint.com/sites/SU-IT/telecom/SitePages/Home.aspx

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings