Calculate difference between dates on a SmartForm taking into account Public Holidays

  • 11 April 2017
  • 1 reply
  • 44 views
B
Badge +1

How do we display the difference between two dates on a SmartForm, taking into account Public Holidays?

 

We have populated Working Hours on the Workflow server, though unsure how to link to it  from the SmartForm.

 

The SmartForm is for a leave request. The user will pick leave start and finish dates and the form needs to display the number of actual days leave, taking into account weekends & public holidays etc. Eg. If someone chooses to takes leave from Thursday 13th April 2017 return Tuesday 18th April 2017, the form needs to display 1 days leave due to Easter public Holidays on 14 & 17th April and Weekend 15 & 16th April.

 

Thanks

1 reply

B
Badge +15

Hi,

 

I do not think there is a way to accomplish this out of the box. There is an expression to calculate difference between two dates, but it does not account for weekends and holidays.

 

I believe there are some SQL scripts out there that can do this calculation. You can create a stored procedure for it, then use SmartObject to call the stored procedure.

 

As for Public Holidays, those are a little tricky. You may need to maintain a separate table to keep track of holidays, then include them into the calculation in your stored procedure.

 

Here's a SQL solution I found on stackoverflow:

http://stackoverflow.com/questions/10330836/how-to-count-date-difference-excluding-weekend-and-holidays-in-mysql

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings