This solution can be used for any SharePoint list, including form and document libraries. The only code is in the calculated column.
The requirement was for the workflow to automatically set the due date for the next step in the workflow, but not have that due date fall on the weekend (when the assignee would not be available).
This was applied to several processes where multiple users were involved in contributing to or reviewing a list item, document or form. In order to make the process complete as quickly as possible, the due date for step 2 would be based on X days after step 1 was completed, and step three on X days after step 2, and so on. If a user in the process completes their task early, the workflow can be completed more quickly than assigning all of the due dates at the beginning of the process.
The solution starts with two SharePoint Designer workflows. The first handles the process flow, from step 1 to step 2 to step 3, etc, responding to the change in the Status field (Wait For Field Change In Current Item). The second workflow runs parallel, starting when the list item gets modified. (This works for items that will only get modified once per step. If an item is expected to be modified multiple times per step, you may want to use a flag field that gets set to “yes/true” when the step is completed, then modified back to “no/false” by the first workflow, and use the flag to determine if the second workflow proceeds or terminates.) The Second workflow waits until the due date, then after the wait checks to see if the step has been completed. If not, it sends e-mail(s) to escalate the issue to whomever needs to be notified.
The Due Dates are determined during runtime. As mentioned in a previous entry, workflows have very limited calculation ability. Using a calculated column in the SharePoint list will allow this to work.
In the case of the particular requirement I worked on, the steps in the workflow were assigned an amount of time (in days) for the step to be completed.
The calculated column looks like this:
=IF(WEEKDAY([Modified]+[Deadline])=1,[Modified]+[Deadline]+2,IF(WEEKDAY([Modified]+[Deadline])=7,[Modified]+[Deadline]+2,[Modified]+[Deadline]))
[Modified] is the modified date (a default column in any SharePoint list), and [Deadline] is the column containing the number of days to complete the step.
WEEKDAY returns a day number (1-7). If the result is one of the weekend days, the calculation adds two days to the Due Date (if it was due on Saturday, it needs two days to bump it to Monday – the next business day; if the Due Date is Sunday, one of the days that the assignee had to complete the step was Saturday, so they will need Monday to make that up, and the next business day is Tuesday).
Have the workflow Pause Until Date, with the calculated Due Date as the date you pause until, before sending the late notice, escalating the problem to the attention of whoever needs to be notified.
Here is the gotcha: Did you want the assignee to have until the end of the day to get it done? If you are using “Date Only” (instead of date and time) and set the Due Date to Tuesday, then Midnight Tuesday morning is when the notice goes out. If this is the case, after the workflow completes the Pause Until Date, set it to Pause For Duration for 17 hours (for a 5 pm deadline, or 24 hours for a midnight deadline, or 16.75 hours if you are evil and want to catch them just before they leave for the day and make them late for dinner).
Working It Out 
Comments on: "Limiting SharePoint Workflow Due Dates to Business Days" (58)
Hi,
You seem to be a bit of a guru on SPD. I am starting out as I need to modify my schools SharePoint site to create automatic application forms for PD and excursions et using infopath to create webform and SPD to create workflow. DO you have any tips on how I can create a form that limits date entry to a minimum date in the future – i.e. so that they can only book an excursion 2 months in advance?
So you want to set up an InfoPath form so that the date field requires the date to be two months or more after the day the form is filled out?
Not a problem.
Create a date field, “MinimumDate”. You can choose to have it shown on the form (read only) labeled as “All travel must be after this date”, or you can leave it off of the form (but still have it in the Data Source).
Set up your date data entry (Date Picker) field “DateField”. In the properties dialog, click Data Validation, and configure it to require the date be filled in and that the date be greater than or equal to MinimumDate (If DateField > Form Options >> Open and Save >> Open behavior >> Rules, create a new rule to “set a Field’s value”, with the field as MinimumDate, and then click the function button (fx). In the formula, Insert the addDays function. This returns the date (or datetime) value equal to the first argument plus the number of days specified by the second argument. The formula displays as addDays(double click to insert field, double click to insert field). Single click the first value, then click Insert Function, and choose Today. Unfortunately, this will wrap the double click to insert field, so you will need to delete the double click to insert field. Click the second, and type the number of days (say 60 or 62 to equal two months, depending on how you want to calculate it). Your formula will look like this: addDays(today(), 60).
Of course there are other ways of doing this, and you could add the complexity of determining how many days are between the 10th (or whatever) day of the month of this month and the 10th day of the month two months from now, but I think the 60 (or 62) day window should be adequate for your purposes.
I hope this answers your question, let me know if you are still confused or if I failed to understand your requirement correctly.
Thanks, Jim! Worked really well in a workflow I did.
You are quite welcome! Glad I could help.
How do I calculate for business days. I need a task to be scheduled 5 business days in advance. Is there another option I can use in InfoPath?
Thanx for your help, Ive been able to get it to work, and also link to what type of activity is selected to automatically change the minimum date. I also want to add the approved item to a site calendar when it is approved- is this possible? Also do you know how to alter the normal calendar views on sharepoint so that their is a term by term view rather than month, week or day. Being able to see 10 weeks at a time (with no weekends) is important for my school. Is this difficult to do? Currently we are using sharepoint 2003 but upgrading to 2010 shortly.
Using an SPD workflow, you can create items in a list, and a Calendar is just a list.
You will need to promote the InfoPath fields for any form data you want to add to the created calendar item.
As far as changing the calendar view, the default calendar views only offer Day, Week and Month. My guess is that to do a 10 week view with no weekend days displayed, you would have to open the calendar page in SPD and modify it. Haven’t ever done that, so I have no advice for you (but would love to hear your solution).
However, since a Calendar is just a list, you could also create a list view. This will only show a list of scheduled events (rather than a calendar-like view), but if you use Groupings in a creative way, you could show items grouped by week and by day, if that will meet your needs.
My solutions are based in 2007 (it has been quite a while since I have worked with 2003, and I haven’t done much with 2010 yet).
Sorry for the delay in response, holidays and all.
Oh yeah the other thing i was trying to get it to do was to also open up conditional fields based on what type of activity is selected. But it seems that the user has to select these alternate sections by hand… any tips.
Conditional visibility responds to changes in other fields.
The most convenient way to make that work is by using a Section control. In the properties dialog, select the Dispaly tab, click the conditional formatting button, and add a condition under which the Section will not be visible, for example ActivityType is not equal to “TypeThatTheFieldsInThisSectionAreRelevantTo” and check the “Hide this control” checkbox.
One Section per ActivityType, and you can use duplicate controls in the sections if there is overlap, or you could use drop-down list boxes with different available values tied to a single data field, or variations for whatever your data needs are for the ActivitiyTypes.
When the ActivityType is equal to any value but “TypeThatTheFieldsInThisSectionAreRelevantTo”, the Section (and all the controls in it) will be hidden from the user.
[…] Limiting SharePoint Workflow Due Dates to Business Days […]
[…] My Top 10 SharePoint Posts […]
[…] Limiting SharePoint Workflow Due Dates to Business Days […]
How would you create a date value so it shows up as MM/DD/YYYY ? Right now it’s just returning a number.
What did you set as the format of the calculated column? The calculated column offers several format options… numeric, text, date, date time… set it for date.
If I have misunderstood or you are still having the problem, let me know, I’ll do what I can to help!
Hi, Jim – Thanks for this post. I am trying use your forumula to calculate a target date. Here, the user inputs a Start Date and Duration of Days for the task completion. Adding these two values, the Target Date gets calculated. However, when the Target Date gets calculated, it has to exclude the weekends and holidays, and I tried to use your formula there. But, being a tyro of SharePoint, I am faltering at it. It does not exclude holidays. Could you please guide? Thanks. Hareesh
I did not take into account holidays, because this is so variable from company to company… and there is no SharePoint calculation (aka Excel) formula for it.
Your question forced me to notice that my calculation is only for due dates of <7 days (because that was the requirment), so for user-generated Deadlines, this could get pretty complicated.
I guess one way to do this would be to create a second calculated column called "Weeks" which would calculate the number of calendar weeks in the number of days provided ([Deadline] / 5) and then add to the formula that the duration was ([Deadline] + (Weeks * 2))
If you kept your company's holiday schedule in a SharePoint list (say, a calendar, for instance 🙂 ), you might be able to add an additional workflow that would query the list for any list items that fall between [Modified] and [Due Date], and if it finds one, send an e-mail to a user to manually adjust the number of days until due (because SPD workflow list queries only return the first instance in a list of matching items, and sometimes holidays come in twos – Christmas Eve and Christmas Day, New Year's Eve and New Year's Day, Thanksgiving and the day after).
If you are using an InfoPath form, you will have more options for processing data from querying SP lists, but to fully account for these circumstances in simple ways, you'll need to use Visual Studio.
Hi Hareesh,
Here’s some options for you, presuming the following:
(A) You have an initiation parameter (or similar) of type DateTime, which I’ll call [Start]
(B) You have an initiation parameter (or similar) of type Integer, which I’ll call [NumDays]
Your request can be broken down into a few subparts. (1) What are workdays? (2) How many weekends are there, if ignoring holidays, when adding N workdays? (3) How many holidays are in a given date range? At least the first two are addressed below.
(1) I’m ignoring the complex problems of people having Wednesdays off. The below only addresses where the workdays are Mon-Fri.
(2) The number of weekends to be included is actually a two-step process. First, you know that there are five workdays. Therefore, for every five full days requested to be added, you want to add seven days. In addition, for any remainder after division (aka Modulo) you want to detect if it’s going to include any weekend days. Conceptually, if you number the start days with Mon=1, Tue=2, …, Sun=7, then you can take the StartDate, and add (number of days modulo 5). If that result is six of higher, you are hitting another weekend with that last partial week being added.
Example:
Given [StartDate] := Thursday, Feb 2, 2012; [NumDays] := 9
SET [Whole weeks to add] := ROUNDDOWN([NumDays] / 5, 0)
SET [RemainingDays] := MOD([NumDays], 5)
SET [TempInteger] := WEEKDAY([StartDate],2) == 4
SET [TempInteger] := [TempInteger] + [RemainingDays]
SET [FinalWeekendDays] := 0
IF ( [TempInteger] > 5 )
SET [FinalWeekendDays] := 2
Then the calculation is:
SET [ReturnValueWithoutHolidays] :=
[StartDate]
+ ([Whole weeks to add] * 7)
+ ([RemainingDays]
+ [FinalWeekendDays]
(3) As for the holidays in a given date range, you’ll have to store that somewhere yourself. Then, loop doing the following (since only one day added at a time, simplifies weekend logic):
SET [TempFinalDate] := [ReturnValueWithoutHolidays]
//REPEAT THIS LOOP WHILE A LOOKUP IS FOUND….
IF (lookup for holiday in range [StartDate]..[TempFinalDate] returns a value)
IF WEEKDAY([TempFinalDate], 2) > 4 // Fri, Sat, Sun
SET [TempFinalDate] := [TempFinalDate] + 3
ELSE
SET [TempFinalDate] := [TempFinalDate] + 1
That’s it. You did the impossible. Enjoy!
Hope that helps!
Disclaimer: I work for Microsoft. Nothing said above necessarily reflects Microsoft views. In fact, it could be flat out wrong. It’s purposefully written in psuedo-code so you have to write and test the final solution, and make sure it works for you. Don’t forget to limit the days to be added to positive values, for example. 🙂
In SharePoint Designer, the SET pseudo code might be one of:
Thanks Henry!
Hey Jim! Great blog. I read through this while searching for an answer to a somewhat related issue. Maybe you or one of your readers can help?
I’m setting up an inventory delivery request custom list in SP to be used between us and one of our suppliers. If we request inventory after 10AM today and 6AM tomorrow, then the product will be delivered by 10AM tomorrow. If the request is made after 6AM today and before 10AM today, the product will be delivered by 3PM today.
I have 2 fields that I’m working with – Request Date & Delivery Date. Request Date is set up as a Date/Time field, and it is auto-populated when the user completes the form with the current date/time. Delivery date needs to be generated after the form is submitted and calculated based off of Request Date.
So – My issue is – How do I calculate that Delivery Date field? I’ve tried using SharePoint Designer, but I can’t find any conditions that will allow me to enter the hour AND current date. If it is a simple formula, and I need to make Delivery Date a calculated value, then what formula can I use? (I obviously have no experience in the formula-writing business!)
Thank you so so much for any assistance you can give!
~Beth
You’ll need to use a calculated column, as the calculation possibilities within SPD workflows is limited.
There are two references (I have included them several times in other posts) that help in creating formulas for calculated colums, here and here. If you have used Excel formulas, these will look very familiar.
You will end up with something like this – A calculated column [Delivery Date] with a data type of date (date and time) with the formula
=IF(HOUR([Request Date])<6,VALUE(TEXT([Request Date],"0"))+.41667,IF(HOUR([Request Date])<10,VALUE(TEXT([Request Date],"0"))+.625,VALUE(TEXT([Request Date],"0"))+1.41667))
The formula checks the HOUR value of the [Request Date], and if it is less than 6, the delivery date is 10 AM on the Request Date ([Request Date] + 10 hours, or .41667 days). If it is greater than 6 and less than 10, the delivery date is hour 15 on the Request Date. If it is greater than 10, the delivery date is one more than the request date, plus 10 hours. Dates are calculated using serial numbers, and formatting the column to Date (date & time) converts the serial number into a human-readable date. (Try setting the data type to number to see what I mean).
Hope that helps!
Awesome, Jim!! Thank you so so much!!!
Hi Jim. Great stuff you have on here, definitely going to aid me in my project here.
What I’m doing is pretty straight forward, for the most part, but I haven’t been working with Sharepoint very long, so I’m still a little green.
What I have is a custom approval workflow for physical access, whether it be a key card, a key, a parking pass, et cetera. When an associate fills out the form, it sends it to their supervisor, who then can approve/reject it. However, I need to set it up so that after 48 business hours, if they haven’t responded, to automatically approve it, and send it down to their manager, and then do the same thing at that point. After the manager it will reach a final approval from a specific person and they won’t be on a time limit.
I hope that makes sense. I look forward to your reply!
Thanks
Zack
I would suggest using a secondary workflow.
Two workflows kick off at creation of the item. The first one manages the process, and waits for the approval flag to be set. The sesond waits for 48 hours and then checks for approval. If the item isn’t approved, the second workflow approves the item and stops (presumably documenting that it was an automatic approval rather than approved by a person). If the item has already been approved, it does nothing and stops.
The first workflow, in the meantime, is still waiting for the approval flag. When the second workflow changes the item to approved, the first workflow sees the approval and resumes.
For two levels like you describe, instead of terminating the secondary workflow after the first approval, you could use a calculated field to determine when 48 hours after the first approval was, and do a second wait until that time. See my post, Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution, which also has references on secondary workflows. Or you could have a third workflow that waits for the first approval, then waits 48 hours. I wouldn’t recommend using a third workflow if you expect the list to get a lot of traffic, as SPD workflows have some overhead.
Hopefully this will get you on your way.
Thank you for the quick reply! This definitely gives me a lot of insight into what I need to do to get this finished up. I’ll try it with two workflows, as this List will eventually see some good traffic.
Thanks again!
[…] Limiting SharePoint Workflow Due Dates to Business Days […]
Hi – I’m hoping you can help me. I’ve got a workflow that simply needs to send a reminder email on the last working day of the month. It needs to be reoccurring so that the same email is sent on the last working day of every month to the same people. I’ve written it using a simple list and the email recipients are set up as a sharepoint group so they’re easy to modify. Where I’m stuck is how to make it reoccurring and how to calculate the days so it’s on the last working day of the month. Your help would be greatly appreciated!
Doing loops – infinite loops at that – in a SharePoint Designer workflow is something the programmers specifically wanted to avoid. Make a mistake, and your whole farm is taken down as the workflow sucks up more and more resources.
There are a couple of ways around it though. Try using a secondary workflow (see my reference here and follow the link). This is probably the solution with the lowest barrier to entry.
You could build a workflow in Visual Studio (which requires knowledge of .Net programming and the ability to create and add solutions to your farm).
Finally, some thrid-party workflow solutions (like K2) offer loops as part of their workflow products. I mention K2 because I have used their product and have some familiarity with it. No actual endorsement should be inferred, per my blog’s policy.
Hopefully this should get you started. After that, Google “SharePoint looping workflows” and see what you get. If that doesn’t get you on your way, post another message here (or shoot me an e-mail, you can find the address on my LinkedIn and on my Resume) and I’ll try to help if I can.
Hey Jim,
I’ve stumbled across your blog trying to find help about a calculated column I need to create in SharePoint 2010. I have a [due date] column, and I have many other calculated columns that will need to calculate a date “x” amount of weeks before it’s due, but always do on the Friday of that week. I’ve been having issues with the WEEKDAY function, can you possibly think of a solution to this?
I’d think it would look something like this for a new column due the Friday 6 weeks before the due date =(6-WEEKDAY(“[due date]”-42)+(“[due date]”-42)
Any suggestions would be greatly appcreciated!!!
OK, let me restate the problem to see if i understand…
[target date] is Friday (WEEKDAY = 6, using the default) of the week X weeks prior to [due date].
Is that correct?
No the [target date] could be any date, but I want the calculated value to always be the friday of the week that the field is due, “x” weeks prior to the [target date]. I’m currently using =[Target Conversion Date]-35 just to test some of my calculated fields, and it displays a date (which I want) 5 weeks (7days*5weeks=35 days) before it’s due.
As soon as I change the formula to include a WEEKDAY function: =WEEKDAY(“[Target Conversion Date]”-35), the only result I get is the value 1 even if the date that was displayed wasn’t a Sunday.
I’m not sure if this has to do with the fact that I limited the field to date & time, but I’m trying to take this calculation one step at a time and I’ve gotten lost pretty quick.
Got it.
First thing to do is remove the quotes around [Target Conversion Date]. You are converting the column reference to a string.
WEEKDAY returns an integer, but your column is a date format, so if, for instance, your WEEKDAY of [Target Conversion Date] is a Thursday, it returns the integer 5, which will display as 1/6/1900, which is +5 of 1/1/1900, the value of 0 in date form. Instead, you need to use the weekday function to calculate an offset to the [Target Conversion Date].
To create the offset, use the weekday to compare to 6, the default for Friday. If you want Friday to be the day of the week you want the date to reflect, use
6-WEEKDAY([Target Conversion Date])
to get the offset. So if the WEEKDAY of [Target Conversion Date] is a Tuesday (3), the offset is +3. If the [Target Conversion Date] is a Saturday (7), the offset is -1.
Since you are subtracting exactly 5 weeks, WEEKDAY([Target Conversion Date])=WEEKDAY([Target Conversion Date]-35)
The full calculation would then look like this:
=[Target Conversion Date]-35+(6-WEEKDAY([Target Conversion Date]))
Walking through this: You take your initial date, subtract 35 days to get 5 weeks prior, then add the offset. This should set the calculated date to the Friday of the week five weeks prior to the [Target Conversion Date].
All hail Jim!!! Thanks so much, the logic seems simpler than I imagined!
You are very welcome! I am glad I could help!
Hi Jim, I’ve been asked to create a calendar in Sharepoint which constantly shows an event 10 working/business days in the future to represent the due date of postal documents sent that day. No need for any emails or alerts to be sent, the event just needs to be viewable on the homepage of the site for employees to see, and refresh again each day to stay current. Is this something you could help with?
Many Thanks
Does the requirement for 10 business days mean the creation of a new event in the calendar, or does it mean updating an existing calendar event to move it daiily to the 10th business day out? Is the calendar event used for something specific? Or do you just need to show what the date of 10 busines days out is?
The event will remain the same and requires nothing specific in terms of content or use, just that the time period of 10 working days is shown from the day it is viewed. My purpose is to show our expected document delivery date through the calendar if the document is sent the same day it is being viewed by the user. i.e ‘if we send the document today, it will arrive on such and such date’ The calendar will only be used for this purpose. So either a new event created each 24 hours or the same event but moving each day would suffice
First of all, 10 business days (assuming no holidays) is always 14 days away (Monday July 9 + 10 business days is Monday July 23. Creating something (timer job, workflow, compiled code) that updates a calendar item dated 14 days ahead to the following day shouldn’t be a problem (depending on you skillset and the access level you have). Even setting the time that the date jump forward happens to 5:00 pm shouldn’t be much of a problem (in fact, it might make things easier).
The first trick is going to be the view – monthly will work fine, until you approach the end of the month, and the monthly display won’t extend far enough in the future to display the due date item.
The second trick, however, adding in holidays, makes setting the date using a calculation an insane thing to try to attempt. Monday July 2 + 10 business days is Tuesday July 17. How is a calculated field going to know that July 4th is a holiday (at least where I am; you may not have that particular holiday…)
You could have a separate calendar that contains holidays as calendar list items, and have your workflow/timer job check the calendar for “blocking” events. In other words, the task logic would go something like “Calculate today’s date +14. Check the holiday calendar – is there an event with a start date on that date? If yes, recalculate the calculated date to equal the end date of the blocking event + the blocking event’s duration. Update the target calendar item (the moving event) to have a start date at the calculated date.” A couple of caveats – you would create one holiday item for each holiday block – A five day holiday would be a single event, with a start date of day 1 and an end on day five. Any holiday that ended on a Friday would also have to include the weekend days as well, so that a five day holiday that started on a Monday would end on a Sunday, not the Friday, and a Friday one-day holiday would start on Friday and end on Sunday.
Which begins to sound like a Rube Goldberg contraption.
A simpler solution would seem to be a Content Editor Web part backed by some JavaScript that just displays the calculated date, rather than creating a workflow/timer job to constantly keep updating a calendar item. Especially if the calendar item isn’t used for anything but providing a calculated date.
With a script, the calculation happens when the page is refreshed, no workflows or timer jobs needed. (But this requires that you know JavaScript, or have access to someone who does.) You can version control the script by using the Content Link to link to a text file stored in a library with versioning turned on (a best practice).
Does this help?
The month view on my calendar is slightly different as I have used quite a bit of css to reduce the size of it and as a result it will show a week in advance which will solve that issue. I’m not great with javascript but I have managed to create some jquery elements on sharepoint pages like toggles and animations using tutorials on websites, so i know how to reference files and input code using the CEWP webpart. I have also created a few workflows but I am relatively inexperienced in them, the process of checking another calendar and adding days on etc does sound like it may be a bit beyond my understanding without further tuition in the subject to be honest Jim. So I think I am best to go with the coding option, what code would be needed to pull this off?
Hi Jim,
From the posts I can see you are the sharepoint wizard and hope you can help me. I have a list of items with a due date and have created a calculated column to see if the status of the list is “ok” or “overdue”; the code is
=IF(OR([Next Due Date]=”Error”,DATE(YEAR([Last Received Date])+1,MONTH([Last Received Date]),DAY([Last Received Date]))<[Next Due Date]),"Invalid Next Due Date",(IF([Next Due Date]<Today,"Overdue","OK")))
My problem is that the calculation is only done to a list item when it is added to the list. Would you know a way to make the "Status" column with the above algorithm run/re-calculate daily?
Thanks in advance
As I have mentioned in another post on using Calculated Columns, the calculation is done anytime the item is saved, either the first time, or any subsequent time (and regardless of whether or not the data was changed when the item was saved).
So, how do you force a recalculation on a daily basis? The answer, as with so many SharePoint things, is “It depends.”
SharePoint 2013 has SPD workflows that do loops. SP2013 is still in beta.
Third-party workflow products allow loops.
A Visual Studio workflow (say, in C#) can also do loops. A timer job with C# or other .Net code could also loop through the list. All any of these things just need to resave the list item as it exists, and the calculated column will update.
On a small list, if you don’t have access to a developer, and you don’t need to keep the original item, you could set up an SPD workflow on create that waits until 2 am or some other time with low usage and copies the list item fields to a new list tiem (creating a copy of itself), then deletes the original item. The new item would then have its own workflow that waits until the next morning, and so on. Not recommended for anything more than a small list.
Hope this helps, let me know if I can clarify any lingering questions.
Hi Jim,
i am a newbie to SP Designer, would be very helpful if it is a simple walkthrough. My requirement is excluding weekends and holidays. Please help me.
There are lots of great resources for learning to use SharePoint Designer to build baisic workfows. Once you have the basics, come back and check out my follow up post with the screenshots,, and you should be able to make it work.
As the requirements I was working with did not include taking holidays into account, it isn’t something I have a ready-made solution for. My guess, though is that you either have a SharePoint list with the holidays (calendar), or can make a business justification for creating one. Have the workflow check the list for items that fall in the date range of the wait, and add that many days to the pause.
Hi Jim
First of all great post!!! I am a SharePoint developer since couple of years but i never had to deal with a ootb approval workflow in shareoint 2007. So here is the scenario:
I have a SP list and an ootb approval wf with 4 approval groups defined. Each approvers has 3 days time to finish their tasks. So lets assume approver group 1 did their job in time and is now pending by approver group 2. Will there be reminder mails sent to the apropriate approval group (here 2) to approve this task? Somehow we have an issue that the reminder mails of overdue task is only sent to the item creator(requester) as cc but there is no ‘to’ field in the mail message. Maybe you have some good links to understand the whole workflow scenario with reminder mails.
If that is not achievable i will just create a SPD workflow and do some hacks to get those reminders to work.
Any help or directions would be really appreciated since i saw you actively answer all the questions.
Thanks and keep up your great work
Cheers jerry
I’ve never had much success using the ootb workflows, so my go-to has always been custom wf, SPD whenever possible to allow business users to reconfigure the wf without having to use a developer resource when the business process changes.
The way I have designed reminders is to use secondary workflows, which work great as long as the list/library is not generating hundreds of items per hour. At that point I would recommend visual studio…
Hi Jim
Thanks a lot for you quick reply!!!!!!
You are right SPD or VS WF are much better way to implement wf. Since we have it better under control whats going on. Well so i guess i will create another spd wf to fire off a reminder mail based on some condition. I think thats the better approach.
Thanks again for you input on this issue.
You won a new subscriber to your post 😉
Cheers Jerry
Hi Jim,
I have been working on another formula to calculate working days but i get a syntax error. Do you know where i have gone wrong?
=IF(AND((WEEKDAY(End Date,2))1),(((DATEDIF(Start Date,End Date,”D”)+1))-(FLOOR((DATEDIF(Start Date,End Date,”D”)+1)/7,1)*2)-2),(((DATEDIF(Start Date,End Date,”D”)+1))-(FLOOR((DATEDIF(Start Date,End Date,”D”)+1)/7,1)*2)))
Many thanks
Ben
Interesting approach! Nothing immediately jumps out at me, but I will take a closer look today and see what I can discover.
Thanks Jim
If i am trying to make it too complicated please tell me.
Cheers
I think the syntax problem is here:
AND((WEEKDAY(End Date,2))1)
Thanks Jim, Not sure how to get round that one. Have i over complicated the calculated field and could it be made simpler?
Here is a reference for the AND syntax and usage:
http://spreadsheets.about.com/od/excelfunctions/qt/20080117_and.htm
You need a comma before the one, and, I suspect, additional arguments.
Want to walk us through what the formula is trying to accomplish?
Thanks for the information Jim, I am just trying to calculate a date range which excludes weekends so that I can create a Sharepoint List (Leave request) to utilise the function. this will then be part of working out a staff members left over holiday.
Hi
I have below porcess :
if I receieve a work Request on Friday and it has a deadline of 2 days, then i would like a formula that should automatically pick the Deadline day/date as Monday instead of Saturday..
Would you please help!!
That sounds like a 1 day deadline (as in, due the end of the next business day, today +1). Use the formula I provided above, but substitute the number 1 for the comumn name Deadline, then follow the instructions in the last paragraph about setting the timing.
Hi Jim,
Awesome Blog! I’ve been struggling with a couple of formulas in SP:
1. The ability to auto calculate aging buz days for issues, risks & action items
2. Calculate the number of hours spent on a defect per week & another column to show total hours spent on the defect. This calculation would pull numbers from a different list in SP.
I’m open to other ideas, such as web parts. Please let me know if this is possible in SP.
Thank you SO much!
HM
1. So for aging business days for and IR&A would be Today – create date – non-business days?
2. This one would require a lot more work, would probably be best as custom development
Hi jim,
Thanks in Advance
Am assigning the calculated column to the Task Due Date field.
Am getting the below mentioned error “Coercion Failed: Unable to transform the input look up data into the requested type.”
Please see the data of the calculated field returned in the history log
Value : datetime;#2013-06-18 22:38:14
[…] This shows how to combine an approach like that into Workflow logic: Limiting SharePoint Workflow Due Dates to Business Days: https://dlairman.wordpress.com/2010/10/14/limiting-sharepoint-workflow-due-dates-to-business-days/ […]