Follow Us:
I’m in the midst of two different major workflows for clients right now, one of them being an HR New Hire Onboarding request and approval process. One small but critical requirement of the process was that each level of approval needed a two business day wait period. I’ve seen different suggested solutions for this on the internet, but nothing super easy and in the context of a workflow, or would fully work in my situation. So I thought I’d share a couple of quick and easy ways to accomplish this.
Before I get into the specifics, let me just give a quick rundown of the different parts of my process that I have going on.
All of the approval is tracked in the InfoPath form, not the workflow. The process goes like this:
The process continues until we hit the CEO at which case we stop and redirect to HR. None of all that matters per say, but I just wanted to give you some context of where we’re at. What I needed was to figure out how to calculate and set a future date 2 working days from that current point in time, which there would be multiple in the same workflow context. But I also needed to set the 2 working days for the END of the day at 5pm.
SharePoint workflow (neither 2007, 2010 or 2013) has a built-in action to do the calculation, similar to like a NETWORKDAYS formula in Excel. Ok, so then I turned to posts online talking about using a calculated column to do the formula. Cool I think we’re on to something. But as I got to thinking, it wouldn’t fly. So I can create a calculated column on my form library, but that is only updated when the form itself is updated. Besides, the Created and Modified won’t work either, because neither would be the exact current date/time. I also didn’t have to write some complex Visual Studio based workflow. No thanks. So I devised a similar solution using a separate SharePoint list whose sole purpose is to do the calculation. It’s extremely simple and works just fine.
Let’s get the basics set up:
Ok, now the fun part – the working days formula. A lot of folks point to this blog for reference, and I did their formula as a starting point. You can also find a lot of general example formulas for calculated columns on MSDN. In the original example, they were calculating 3 business days, but I needed 2. So I adjusted the formula to the following, and pasted it in the calculated column:
=IF(OR(WEEKDAY(CurrentDate)=1,WEEKDAY(CurrentDate)=7),CurrentDate+3,IF(OR(WEEKDAY(CurrentDate)=5,WEEKDAY(CurrentDate)=6),CurrentDate+4,IF(OR(WEEKDAY(CurrentDate)>1,WEEKDAY(CurrentDate)<4),CurrentDate+2)))
To be sure it works, create 7 list items (one for each day of the week). Use the day of the week for the title, then just pick a matching date for that day, for each day of the week:
This is only to ensure that the formula will work properly for each day of the week. Check it to ensure the day counts are correct. Once done, you can delete them all if you like or leave them, doesn’t matter. I deleted them and just left one item. Now we turn our attention to the workflow. As a reminder, our reusable workflow is running on the forms library. What we want to do is in context for each approver level, perform the following process:
Works beautifully! I have 3 layers in my workflow, and I repeat the same process for each layer of approver. I use all the same variables except for the Level1DueDate. I make a new variable to keep the final 2 working days due date for each approver due date, like Level2DueDate. Each approver gets the email, opens the form and clicks an Approve button which stores their username and date/time in the form and saves. Once the workflow wakes up from pause, we check if that level approver name is blank, and act accordingly. Mission accomplished!
What if you don’t want to use another list, and purely want a workflow? Do I have to write a Visual Studio workflow? Nope! Yes, you still have to use custom code, but guess what. There are multiple software vendors that make workflow extensions and guess what they can do – provide an action to execute code in-line at runtime! I haven’t researched extensively, but I know of these two in no particular order:
Just add your favorite execute custom code action, paste in the C# code to do the calculation, and you’re ready to go!
I’ll freely admit I haven’t tested this, but it would work in theory. In the free edition of the Harepoint extensions, it has some DateTime functions. One I noticed was:
So you could in theory, do all the formula with workflow actions. This returns a numeric value for the day of the week (1 for Sunday, 2 for Monday and so on). Then you could say if 1, add 2, if 2, add 2, etc. Basically doing the same thing as your calculated formula, but with workflow actions.
At this point you should have a couple ways to accomplish whatever you’re trying to do with working days. I hope it was useful!
If you need assistance with or would like to discuss your HR New Hire Onboarding processes or any other SharePoint workflows, Contact Us!
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
Request Download
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.