Workflow Due Date as Next Business Day

Posted on by 9 comments

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
    ch5[94]
  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:
    ch5[95]
  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:
    ch5[96]

    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:
    ch5[97]

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.

tagged with , , , ,

9 comments

  • John
    July 20, 2011 12:25 pm

    So, I try to put this together within my local SharePoint 2010 instance, and the workflow’s won’t start at all… Thoughts?

    Reply
  • Laura
    July 25, 2011 2:41 pm

    Tip: If you’re logged in as “System” account, workflows will never automatically run on create or change.

    Reply
  • RG
    December 11, 2011 7:03 am

    Hi Laura, With this workflow you saved me my day one more time. Thanks. I have a question when you say: It entails creating a lookup list of every day of the year. Is this literally a list with 365 entries? How can I handle the holidays? I have a task list and I need to use this workflow to set the due date respecting weekend and holidays. Sorry for my English. Thanks in advanced.

    Reply
  • Ramanjulu
    September 25, 2012 4:38 am

    i am not able to grab the item which i have created and copy that in original date field down the page. can you please suggest me how to do this.

    Reply
  • KarenD
    June 20, 2013 8:28 am

    Workflow Due Date as Next Business Day – on this topic what exactly does the list look like can you give me a screen shot? Also on the column “Day of week” I selected Calculated column and put in =WEEKDAY[Original Date] using insert column for original date and then selected number and it will not work?

    Reply
  • KarenD
    June 20, 2013 10:36 am

    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.

    In datasheet view I am suppose to put a new original date 06/21/2013 till the end of the year? Or repeat the original start date all the way down the rows to 365 days?? so it will not allow me to add a new row I get this error message “Changes to the current row cannot be saved due to invalid data or missing data in required fields”

    Reply
  • Laura
    August 5, 2013 2:00 pm

    RG,
    Yes, it’s a list with 365 entries. Just remove the ones for holidays. If 11/25 is a holiday, then the day before it will have a next due date column of 11/26 instead.

    Karen, it sounds like you have most of it right. If you’re getting the error, it sounds like you’ve set some field to required and are not filling it in. Make sure no fields are required.

    Reply
  • Maura J-O
    January 7, 2019 4:11 pm

    Can you explain the removal of holidays a little more? I created a list with EVERY day of the year, including weekends and holidays. How does removing the holiday update the due date column?

    Reply

Leave a Reply