How can I calculate business days in Nintex New Responsive Forms?

EuanGamble
Userlevel 5
Badge +13

I was recently asked how to calculate business days within a Nintex Form. 

 

Here is a video on how I achieved a result, but this is just a quick example and still requires thorough testing to see if it meets your business requirements.

 

 

Here is the function:

 

 

ifElse ( /* IF MORE THAN 1 WEEK DIFFERENCE */ dateDiff("TotalWeeks",[Form].[Start date],[Form].[End date]) != 0, /* SUBTRACT 2 DAYS PER WEEK FOR WEEKENDS */ (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1) - (2 * dateDiff("TotalWeeks",[Form].[Start date],[Form].[End date])), ifElse ( /* SUBTRACT 2 DAYS */ (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 7) || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 6 && formatDate([Form].[Start date],"dddd") == "Tuesday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 5 && formatDate([Form].[Start date],"dddd") == "Wednesday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 4 && formatDate([Form].[Start date],"dddd") == "Thursday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 3 && formatDate([Form].[Start date],"dddd") == "Friday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 >= 2 && formatDate([Form].[Start date],"dddd") == "Saturday"), (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1) - 2, ifElse ( /* SUBTRACT 1 DAY */ (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 6 && formatDate([Form].[Start date],"dddd") == "Monday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 5 && formatDate([Form].[Start date],"dddd") == "Tuesday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 4 && formatDate([Form].[Start date],"dddd") == "Wednesday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 3 && formatDate([Form].[Start date],"dddd") == "Thursday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 2 && formatDate([Form].[Start date],"dddd") == "Friday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 1 && formatDate([Form].[Start date],"dddd") == "Saturday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 2 && formatDate([Form].[Start date],"dddd") == "Sunday") || (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1 == 1 && formatDate([Form].[Start date],"dddd") == "Sunday"), (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1) - 1, (dateDiff("TotalDays",[Form].[Start date],[Form].[End date]) + 1) ) ) )

 

 

 

Cheers,
Euan

13 replies

S

Thanks Euan for this. 


How do we modify the formula to achieve number of working days per month?  


 


Example. Input
Start Date: 01-01-2020


End Date: 02-29-2020


 


Result


No of Days in


Jan 2020: 23


Feb 2020: 20


Mar 2020: 0


and so on...

EuanGamble
Userlevel 5
Badge +13

Hi @shadab,


 


So you want a month breakdown after the calculation on top of this calculation?


 


Cheers,


Euan

S

Thanks for your response. Not sure what you mean by manually count the days but here is a scenario. If a project start date is 24th August to 08th of Oct, calculating business days becomes slightly trickier. How best is to modify the formula you had shared to auto calculate 5 days in Aug, 23 days in Sept and 6 days in Oct?

EuanGamble
Userlevel 5
Badge +13

Hi @shadab,


 


I reread your message - I thought you wanted to just know business days in a month, but what you want to know is a break down of business days, per month.


 


Sorry, I don't have a recommendation on how to calculate the output you are after.


 


Cheers,


Euan

S

The scenario is: our sales team will add their pipeline data in the online form which will have project details, resource details, pricing details etc. Once the resource start & end date is added (that can be any date range under the sun), the form should calculate the revenue based on the number of business days for that project for that duration. Thus only after the user verifies the total revenue projection is correct he will then go ahead and submits the form.  

EuanGamble
Userlevel 5
Badge +13

Hi @shadab,


 


Thanks for providing your use case. Unfortunately, this scenario cannot be fulfilled using the functions currently available in Nintex Forms.


 


Cheers,


Euan

S

ok, thanks Euan! Can I expect this to be part of your product pipeline? If so how soon can we expect this function to be included into the new release? 


 


For now we will develop a web service that will do the calculation and then use a web request control in a New Responsive form to call the service and do the calculation. That is what we were advised by your support team. 


 


Also just to clarify the scenario further - the reason why we want to have the month-wise split of working days in a month is because some of these months will have public holidays (like July 4th for US, Australia Day 27th Jan etc.) which will be non-working days. These may or may not overlap with a weekend. Thus we also want to factor such holidays from revenue calculation standpoint. We cant factor a holiday in a month if we get a whole number i.e. 62 days as an output (assuming a project starts on 17th Aug and ends on 06th Nov 2020). 

EuanGamble
Userlevel 5
Badge +13

Hi @shadab,


 


So usually when I am trying to understand something like this, I will try and understand it using Excel or JavaScript. When looking at your scenario, I don't think there is any way of solving this with even with new functions. It's just too complex for our formula builder. It really needs to be externalized as you mention. The challenge you have is that our Web Request control currently only supports a 'Dropdown' interface. While you can pass a web service parameters, like 'Start date' and 'End date', you would need to make a choice from a dropdown to execute the query. I assume you would need to select some type of additional parameter, like 'Office' or 'Project type' for the execution to occur. I do agree however, that this type of logic is definitely better served in a programming language served by a web service, like Azure Functions.


 


Cheers,


Euan


 


**Edit** Sorry, we also have the 'Text - Short' option in the web request control, but again it will not meet your formatting requirements.


 

S

The holidays we are able to address separately as we multiply holiday loss factor (0.8 or 0.9) etc. depending on a month.


 


The only value we need is business days month wise, in the given date range.


Once we get that rest of the other calculations we are able to manage.

S

Thanks @EuanGamble for explaining. We really appreciate your time.


 


Regarding Web Service: We have taken help from someone in our company to build the API. 


I think Nintex form needs JSON output to process the calculation further. The output that we now have is in the following format.


 


{"Result":"Aug2020-6,Sep2020-22,Oct2020-22,Nov2020-21,Dec2020-23,Jan2021-8"}

This output is getting generated from the date range https://<domainname>/api/GCG/GetBusinessDays?StartDate=2020-08-24&EndDate=2021-01-12


 


With this we now get 6 working days for Aug2020, 22 for Sep2020 etc. 


Are we on the right track?

S

@EuanGamble 


Please share the Nintex syntax to loop the days in order to get the month-wise split.


For e.g. we have 'for' or 'while' loop in C#.


If we get the equivalent syntax then we will try to build the logic to get month-wise business days.

EuanGamble
Userlevel 5
Badge +13

Hi @shadab 


 


Someone from the support team will reach out to you.


 


Cheers,


Euan


 


 

C
Badge +1

Hi Euan,


 


Thanks for the example. I'm trying to calculate the number of days using the following formula for a form variable:


 


dateDiff("TotalDays",[Context].[Current date],[Form].[LastCommunicationPetitionerDate])


 


When I add the variable to a form and i enter the LastLastCommunicationPetitionerDate, I get blank. The variable is an integer so i'm not sure what I am doing wrong.


 


Thanks..


 


 


 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings