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...

Hi @shadab,

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

Cheers,

Euan

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?

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

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.  

Hi @shadab,

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

Cheers,

Euan

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

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.

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.

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?

@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.

Hi @shadab 

Someone from the support team will reach out to you.

Cheers,

Euan

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..