SharePoint Designer 2007 – Business Days Task Reminder Workflow

1. In this article we will discuss how to use SharePoint Designer (SPD) 2007 to create a Business Days Task Reminder Workflow. Here is our scenario:

   a. A business leader assigns a task or tasks to an employee with a due date

   b. The employee receives an initial task alert e-mail message

   c. Four business days before the due date, if the task has not been completed, the employee receives a reminder e-mail

   d. Two business days after the due date, if the task has not been completed, the employee receives the first late reminder e-mail

   e. Seven business days after the due date, if the task has not been completed, the employee receives the first second reminder e-mail

2. To setup our environment we will need to create a custom task list. From the front page of your SharePoint site follow this click path: Site Actions -> Create -> Tracking -> Tasks. Name the new list “Business Days Task Reminder Workflow”. Choose “No” for the “Send e-mail when ownership is assigned” function. The list will have the following standard fields:

• Title - Single Line of Text
• Priority - Choice
• Status - Choice
• % Complete - Number
• Assigned To - Person or Group
• Description - Multiple lines of text
• Start Date - Date and Time
• Due Date - Date and Time

3. To build this application we will need four custom components, the SPD workflow to send the e-mail alerts, and three calculated columns to define the proper business day ranges, i.e. four days before due date, two days after the due date, and seven days after the due date.

4. To build the calculated columns we need to first configure our calculated columns to count business days (Monday through Friday) and ignore weekends (Saturday and Sunday). This can be done using the calculated column’s Weekday function and some simple conditional logic. The Weekday function returns an integer for each day of the week, as shown below:

• Sunday 1
• Monday 2
• Tuesday 3
• Wednesday 4
• Thursday 5
• Friday 6
• Saturday 7

Now using the Weekday function we can use some simple conditional logic to determine business day, such as shown below:

   If Weekday > 1 and Weekday < 7

5. Unfortunately it’s not enough to simply know that the date is a business day, we also have to know how many business days it is from the Due Date, and we have to know this for both before and after the due date. So let’s look at the logic to determine these dates, first here is the logic for calculating 4 business days before the Due Date:

   =IF(WEEKDAY([Due Date])<6,[Due Date]-6,IF(WEEKDAY([Due Date])=6,[Due Date]-4,[Due Date]-4))

Essentially this logic can be explained as follows:

   • If the Due Date is before Friday, subtract 6 from the Due Date to derive the date 4 business days before the Due Date

   • If the Due Date is a Friday, subtract 4 from the Due Date to derive the date 4 business days before the Due Date

   • We don’t need logic for a Due Date of Saturday or Sunday since those aren’t business days

6. Next, here is the logic for calculating 2 business days after the Due Date:

   =IF(WEEKDAY([Due Date])>4,[Due Date]+4,IF(WEEKDAY([Due Date])>1,[Due Date]+2,[Due Date]+2))

Essentially this logic can be explained as follows:

   • If the Due Date is after Wednesday, add 4 to the Due Date to derive the date 2 business days after the Due Date

   • If the Due Date is after Sunday, add 4 to the Due Date to derive the date 2 business days after the Due Date

   • We don’t need logic for a Due Date of Saturday or Sunday since those aren’t business days

7. Finally, here is the logic for calculating 7 business days after the Due Date:

   =IF(WEEKDAY([Due Date])<5,[Due Date]+9,[Due Date]+11)

Essentially this logic can be explained as follows:

   • If the Due Date is before Thursday, add 9 to the Due Date to derive the date 7 business days after the Due Date

   • If the Due Date is Thursday or later, add 11 to the Due Date to derive the date 7 business days after the Due Date

8. Next we need to add all the other lists elements as shown below (Picture1):

There is one other calculated column, Start Date – No Time, its calculation is shown below:

      =TEXT([Start Date],"mmm-dd-yyyy")

And finally, there are two choice columns, they have these values:

   • Priority:

       (1) High

       (2) Normal

       (3) Low

   • Status:

      No Action Taken

      Approved

     Denied

9. Now that we have all the date calculations and other columns in place we can easily create a workflow to send reminders on those dynamic dates. The workflow will be configured to run on "Item Created". Our workflow will have the following four steps:

   a. Send Initial Email

   b. Remind 4 Days Before

   c. Remind 2 Days After

   d. Remind 7 Days After

10. Here is the logic for Step 1 – Send Initial Email (Picture 2):

The “Due Date – No Time does not contain Dec-30-1899” and the “Start Date – No Time does not contain Dec-30-1899”. These conditions are present to prevent the workflow from running if the user has not provided a Due Date or a Start Date. Normally these would be mandatory fields but in this case the customer wanted the ability to do a draft task, and not have to worry about the workflow sending out partial data. If the user doesn’t supply a Due Date or a Start Date the calculated column will generate a date of Dec-30-1899 (because the date is blank) and the workflow will not start.

I use a Boolean called First Run to tell me if the workflow has been rerun and if so to protect against certain conditions causing errors. As you will see, all the other steps will only be run if First Run equals “No”. The First Run value is changed when the Action “Update Items in Tasks” runs (the third Action in this step).

The initial e-mail is sent to the Assignee in the second Action of this step. Obviously the content of the e-mail can be whatever the user requires but essentially it is informing the recipient about the details of the tasks they must perform.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Minus 4 BDs”.

11. Here is the logic for Step 2 - Remind 4 Days Before (Picture 3):

In the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The second e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but basically it’s a reminder that the task has still not been completed.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Plus 2 BDs”.

12. Here is the logic for Step 3 - Remind 2 Days After (Picture 4):

Again, in the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The third e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but again basically it’s a reminder that the task has still not been completed.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Plus 7 BDs”.

13. Here is the logic for Step 3 - Remind 7 Days After (Picture 5):

Again, in the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The final e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but basically it’s a notice to the end user that the task has still not been completed within the allotted time.

14. OK, now both our calculated columns and our workflow are ready! Now let’s try out our application. Create a new task in the task list. Fill out the task as normally but in the Status field choose “No Action Taken”. For testing choose yourself as the “Assigned To”. Select the current date as the “Start Date”, and a day a week from now as the “Due Date”. Finally, click the OK button to start the workflow.

15. If everything is built correctly the workflow should send out an initial e-mail, a reminder e-mail 4 business days before the Due Date, another reminder e-mail 2 business days after the Due Date, and a final reminder e-mail 7 business days after the Due Date.

16. As always the power and utility of SharePoint Designer workflows amazes me – all this function without any code – now that is awesome!


I hope that helps!

Tom Molskow