Workflow Due Date as Next Business Day

In a recent workflow, I was challenged with making sure that when tasks get assigned, they are never due on a weekend or holiday.  The following is the solution that I came up with.  It entails creating a lookup list of every day of the year.  This way, not only can we ensure that we don’t assign tasks to be due on weekdays, but we can go into this list and define each of our company’s holidays.  So, you’ll wonder why the “Next Due” field isn’t calculated.  It’s because they have to be able to go in and modify any of these if they’re on holidays.  We create “Next Due” value with a workflow instead of as a calculated column.

We’ll use a lookup table of dates, so that when we need to set a due date in our main workflow, we’ll be able to lookup to this table.  Every day of every year will be represented here, so the workflow will look up the matching day

1. Create a list on the site called Days of the Year
2. Create the following columns on the list
   1. Original Date – date field
   2. Next Due – date field
   3. Day of week – calculated column – it’s a number and the calc is =WEEKDAY(OrigDate)
3. Create a SharePoint Designer workflow on this Days of the Year list.
4. The workflow runs only each time an item is created
   
5. Create a new item in the Days of the Year list, and only fill in today as the Original Date.  You’ll see that the workflow will run and will fill in the rest.
6. A bit of cleanup: In the list’s settings, the Title field is required by default.  Change this to NOT required. In the list’s advanced settings, change the management of content types to YES.  Click the name of the Item content type, and set the Title and Original Date fields as hidden.
7. Switch over to datasheet view in the list.  Grab that first item you created, and copy that Original Date field down the page to create a bunch more… to represent the whole year.
8. In your main workflow, the one based on a library or whatever, the one that you’re assigning tasks from…. this is where we’ll go next.
9. Before your task needs to get assigned, the idea is that instead of saying that the task is due in 1 day or whatever, you’ll be looking up to the Days of the Year list to find the proper business day.
10. In this example we’ll say the task needs to be due in one day.  In the workflow (before the actual task action), add this action:
    
11. Click the Local Variables button at the top, and create a variable called NextDueDate, as a date/time variable.
12. Use the Set Workflow Variable action to set the NextDueDate variable to:
    

    So you’re looking at the  Days of the Year list, with the result needed being the Next Due field.  In the Find the List Item section, you’re matching something from the current list with something in the other list.  In this case, the field Original Date in the other list will match up with the Tomorrow variable.  What gets returned is the next business day.

13. In your task that’s getting assigned, in your main workflow, use the NextDueDate variable as the due date:
    

In summary, this isn’t a completely perfect solution for every situation, but if you try it out, maybe it will give you ideas and you’ll be able to tweak it to suit your needs.  The concept of a lookup table in general, is just good to know and can be pretty powerful.