You are viewing limited content. For full access, please sign in.

Question

Question

How can you Exclude Weekends and Holidays from Date Token Calculator?

asked on February 4, 2014

Inside the Business Process Steps, you have the ability to exclude Weekends and Holidays which is great when calculating out a due date.  Does anyone know how to have the normal date token calculator do the same thing?

 

It sure looks like Workflow can do this based on the items inside the Business Process Step, so a separate script shouldn't be necessary.

 

Custom date/time Token Calculator maybe?

 

Any ideas?

 

 

 

2 0

Replies

replied on February 4, 2014

The Holiday and Weekend data is indeed stored in the Workflow SQL database.  It is XML encoded data in the workflow_external_objects table with an object_type = 25.

 

If you were really determined you could parse that XML data to get the rules for observed Holidays and build some type of script activity (or custom workflow activity) to use those rules in a date calculation.

 

Even with that though it appears that the Easter and Chinese New Year Holidays are not contained in the XML data.  They are using complex calculations (most likely function calls) embedded within workflow itself to make those Holiday calculations.

 

So the quick answer is that the 'juice might not be worth the squeeze'  sad

 

Since the logic already exists within workflow then perhaps someone could make a feature request for LF to include this functionality in the Date Token Calculator for a future release?

 

 

3 0
replied on March 31, 2015 Show version history

Late post to this thread; I created a custom workflow activity that will take two datetime values and determine the difference between them.  The activity will determine the difference in seconds, minutes, hours, days, business days, weeks, calendar weeks, months, quarters, and years.  The business days calculation uses the Workflow SDK to get the Workflow settings for Holidays and Weekends.  Here are some screenshots;

The custom activity will allow you to create multiple tokens.  In this case two tokens are created, one token called 'Days' to determine the number of days between 01/01/2015 and 03/31/2015 and the second token called 'Business Days' to determine the number of business days between those same dates...

This image shows an edit of the 'Business Days' token...

This image shows the results...

 

The DateDifference activity is available at www.qfiche.com as a free download.  (The download will install a 30-day trial activity.  When you register the activity I will provide a free production key.)

Caveat  - The activity has been tested on WF 9.1 and 9.2 but since I am accessing the Holiday and Weekend data via the Workflow SDK those function calls might change with future WF versions.  My intention though is to keep this activity current and tested on future releases as well.

 

2 0
replied on February 4, 2014

Holidays can be added inside the Workflow Admin Console, so can be customized based on the customer needs and enable/disable/add the holidays you want to observe.  It is pretty slick.  My problem is that the "exclude Weekends and Holidays" option is only inside the Business Process Activites and even in that can't be passed a custom date to work with.

I was hoping to avoid a script.  It sure seems like it is built into Workflow as it appears in the Business Process Steps.  There just isn't an activity to do it for a normal workflow... yet.

1 0
replied on February 11, 2014

Our workaround was to create a simple Access db and filled in the holidays from there.

Worked flawlessly.  We went Access so that the customer could easily edit holidays for their firm yearly as they are announced.  We essentially used Ken Salomon's tips above to create the workflow.  This customer also needed a date returned for different deadline requirements (14, 30, 60 working days), so we built the workflow to stand alone and be invoked by the other workflows.

These other workflows would pass the start date and business days as Inputs to the data calculation workflow and would get returned the formatted date excluding weekends and holidays.

 

Thanks Ken.  Your tips pointed me in the right direction.

1 0
replied on February 4, 2014

Date Token Calculator does not have that feature.

0 0
replied on February 4, 2014

Is there any way to do it without a script?

If a script is the only way, can anyone post one?

It would be great to have that as a new feature.

0 0
replied on February 4, 2014

Ken - Interesting question.  I could see how you could determine the Weekends and exclude them from a date calculation but how would you determine Holidays? 

 

Would you be working from an internally maintained Holiday list or use something like a list of Federal Holidays?  Without going the self maintained list route you would have to jump through a bunch of calculations to determine the Holidays for any given date range.  If you go the Federal list of Holidays you would also have to determine which Holidays to include and which to exclude (as many businesses ignore some of the Federal Holidays).

 

Then also I assume that you are only concerned about US Holidays?  wink

 

Still, with all of that I think it might be do-able with some type of script activity or custom workflow activity but it would take quite a bit of work!

0 0
replied on February 4, 2014

Ok, so if Workflow keeps track of it then I would think that the Holiday data might be in SQL somewhere.  Let me poke around a bit to see if I can find it. 

0 0
replied on February 4, 2014 Show version history

This information may very well indeed be in a SQL table somewhere.

 

If we had this table location or even created our own SQL table, we can use the date calculated by the date calculator and then increment the date accordingly and test against the database table to confirm everything is up to snuff with the newly calculated date.

 

You can use the following activities to accomplish this:

  • Assign Token Values - Create a token with the calculated date (minus 1 day)
  • Repeat - Repeat if 'Query Data' Result Count is not 0 and to test this after the contained activities have run.
    • Date Token Calculator - Add 1 day to the date token
    • Assign Token Values - Store new date value to token.
    • Query Data - Query the database column that contains holiday dates and weekend dates to see if they return a result when given our date token.
1 0
replied on February 4, 2014

That would be ideal to have the feature added. 

0 0
replied on January 6, 2015

I see in this post that it can be done but this will take time to implement, need to create a database for this and there is no real UI for data entry by the customer (we also need to build this UI).  Laserfiche should be offering this as there is already a Holiday/Weekend engine in the Workflow Admin Console and this feature is already available in some of the activities.  I really think that Laserfiche should be offering this.

I am mainly interested in getting a function where I can enter a Starting Date and an End Date, and the return value would be the number of working days between these two dates.

Can Laserfiche create that activity?

0 0
replied on January 6, 2015

I understand why you would want to do that, but why not create a web service or a script in a workflow "script" activity where you have those two inputs and have the output. 

You might just be able to create a database that contains the days of the year, and what work day it is, like, january 2nd is the 1st workday of the year, January 5th of this year was Work day 2. Then you can use a stored procedure or the token calculator to subtract the start date # from the end date # and you instantly have your amount of work days.

Maybe this is not the most efficient way of handling things, but it shows how we have many ways to solve this problem. I do agree though that since this is a business tool, making some of these calculations easily accessible would be highly convenient and ensure accuracy as well as consistency so that we have a clear location of where to have the holiday information and such.

0 0
replied on January 7, 2015

This why I am asking Laserfiche to provide such activity, for simplicity of implementing and usability by customers.  We are not all programmers even if  some of us can.

We will see if Laserfiche can help us or give us a sample SDK script to interact with their internal holiday engine.

Laserfiche developers comments are welcome :)

0 0
replied on January 7, 2015

Daniel,

I can take a look at the Workflow weekend and holiday data schema again to see what would be involved.  I already have a custom workflow activity that accepts two date/time values and determines the difference between them in several intervals (seconds, minutes, hours, days, weeks, calendar weeks, months, quarters, years).  Perhaps I can reach out to LF to see if they will allow me to access their internal Workflow business day functions and incorporate that into the custom activity.  My existing 'Date Difference' activity is available as a free download from the Products page at www.qfiche.com.   If you send your email address to cprimmer@qfiche.com I will let you know how Laserfiche responds to the request.

1 0
replied on March 31

Is there any progress on this from the Laserfiche perspective as far as including this ability in the date token calculator?  The last post is pretty old.  I thought I heard that ver 10.2.1 might have the ability to use the date token calculator to "add" or adjust a given date by the number of "business days".  This is needed to calculate SLA's in a lot of business applications, where we need to calculate a "due date" for a process based on a number of business days since the process started.   Then we can trigger a message indicating processes that are due tomorrow or the next day, etc.

0 0
replied on March 31

The day calculations in Token Calculator (WorkDate, NetWorkDays) take into account holidays specified on the Workflow Server in 10.2 (they always included weekends). 

0 0
replied on March 31

Thank you.  I am getting a formatting error.  Is there some special formatiing I need for the date?

See below:

I enter: WORKDAY(3/15/2017,4)

into the test formula and get this result.

 Invalid Date Value:  Function: WORKDAY Expression: WORKDAY(3/15/2017,4) Message: 0.000099157164105106593951413

I have tried several versions of the date and get the same result

0 0
replied on March 31

WorkDay doesn't take static date values directly. You have to format it as a date: WORKDAY(Date(2017, 3,15),4). This formatting is not necessary for tokens: WORKDAY(%(Date),4).

0 0
replied on March 31

Got it.  Boy, I would never have guessed that.  Should put that piece of information in the admin help files.  (Or at least I couldn't find it) 

 

Thanks for your help.

0 0
replied on March 31

No problem. I'll pass your suggestion on to our User Education people.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.