Workflow–Extracting Date Information
Someone recently had a question on one of my workflow blog posts. The goal is to set a deadline of the 14th of the month for one document type, and the 29th of the month for another type. This is going to entail using a workflow to figure out what the create date is, and then assign a due date relative to that. This can be accomplished using some really useful actions in a workflow, and it applies to 2010, 2013, and Office 365.
What do I need for this example? A list or library with a due date field added, and a choice field called document type, where the options are “Policy” or “Contract”.
It all starts with obtaining an ISO formatted date, and using it as a variable…
- Add the action: Set Workflow Variable
- Click the blue words workflow variable, and click Create a new variable…
- I’m going to base mine off of the Created date field, so I’m going to name the variable Created ISO. Make sure you select String as the Type. Click OK.
- Click the blue word value, and then click the function (fx) button. Leave the data source as current item, pick your date field as the field from source, and for Return field as, be sure and pick ISO Formatted. Click OK.
- Now you need to see what this format looks like, so you can work with it and get what you need out of it. For the next action in your workflow, do a Log to history list. Click the blue word message, and then click the function (fx) button.
- We can just look at this value as a string in our log, to use it as a reference, so we will know what the syntax is. Data source is Workflow Variables and Parameters, field is the Created ISO variable, and return field as String.
- We’re going to publish it next, to see what the result looks like. If you’re using SharePoint 2013 or SharePoint Online, you’ll see a Transition to Stage section in your workflow. Put GoTo End of Workflow in there. Here’s what your whole workflow looks like so far:
- Publish the workflow, and run it on an item in your list or library that you created the workflow on. Click the Workflow Settings button in the ribbon, click Open Associated list, and then hit the F12 key to quickly get there. On my status page, this is the information that was logged by the workflow.
If you’re new to SharePoint, here’s some basic info on how to get to the workflow status screen for an item, in addition to a ton of other workflow beginner basics.
- The syntax of the ISO formatted date is as follows:
Just break it down to where all of the important parts are. The first character is considered 0.
Year: characters 0 through 3
Month: characters 5-6
Day: characters 8-9
If you live in a different country, your date may have a different syntax than mine. Obviously you can see that the time is here as well, if you need to extract that info for any reason.
- We need to get the month. Go back to your workflow in SharePoint Designer. After your Log action, add the action called Extract Substring of String from Index with Length.
- Click the blue word string. Click the fx (function) button and choose the data source of Workflow Variables and Parameters, the Field is Variable: Created ISO, and return field as string. Click OK.
- Where you see Starting at 0 for 0 characters, click on the first zero. Type a 5. Click on the second zero, and type 2.
- Where it says Output to Variable:substring, click the name of that substring. Choose Create a new variable… Name it Created Month, and click OK.
- Repeat step 11. Where you see Starting at 0 for 0 characters, click on the first zero. Type a 8. Click on the second zero, and type 2. Create a new variable again, name it Created Day, and click OK.
- Repeat step 11 again. Where you see Starting at 0 for 0 characters, click on the first zero. Type a 0. Click on the second zero, and type 4. Create a new variable again, name it Created Year, and click OK.
- We are going to need to be able to do math on these numbers that have been extracted from the created date. Right now they are string variables. Next, we will convert each one to an integer variable. Put your cursor after the action that you created at step 13. Add the action called Set Workflow Variable.
- Click on the blue words workflow variable, and click Create new variable… Name it MonthInt, and the type is Integer. Click OK.
- Click on the blue word value, and click the fx (function) button. The data source is Workflow variables, field is Created Month, and return field as integer. This basically takes the text value that we already stored in the Created Month variable, and converts it to an integer. Click OK.
- Do the same thing after each of the other two portions of the date, the day and the year, adding two more variables also. The workflow should look like this so far:
- Since the type of document called “Policy” is due on the 14th of the month, we need to look at the created date and see if it’s before or after the 14th of the month. If it’s before, then we can set the due date to this month, if it’s after, the due date needs to be the 14th of *next* month. Add a condition to your workflow. If any value equals any value.
- Click the first value, and select the Document type field from the current item data source. Click the second value, and choose Policy.
- Inside of this condition, add the action Set workflow variable. Create a new variable called Due Day of Month, as an Integer. For the value, type the number 14.
- With the cursor on the action you just added, click the Else Branch button in the ribbon. Under the word Else, add the action set a workflow variable. Set the Due Day of Month to the value 29. We only have two policy types in this library, so our else statement is simple, if it’s not a policy, it’s a contract.
- Outside of the condition you just added, after it, add the action Do Calculation.
- Click the first blue word value and click the function (fx) button. For the data source, select Workflow variables and parameters, and for the field, pick Variable: DayInt
- Change the word plus to minus. Click the second blue word value, and use the function button to pick the workflow variable called Due Day of Month. Where it says Output to, click the drop-down and Create a new variable. Call it Subtraction Result. This is what the action looks like now:
- Under the calculate action that you just added, add another condition. If any value equals value. For the first value, pick the Subtraction Result variable, and then select is less than, and type the number 1.
- Now we have to construct a new variable using the date of the due day of the month. We will create it as a string, and then we will have to convert that to a date variable. Under (within) the condition you just added, Add the action called Set workflow variable. Create a workflow variable called “Due Date String”, as a string variable.
- Click the blue word value. Click the little ellipsis (and not the fx button).
- In the String Builder, first use the button Add or Change Lookup to insert the variable called Created Month. After that, type a backslash, and add the next lookup, etc, so that your full string looks like this. This is formulating the date of the 14th of the month, at 8AM.
- Next, we’re going to put in the else condition, though, so that we can say what the date will be of the 14th or 29th of *next* month. Click the Else Branch button in the ribbon. Under the word Else, add the action called Do Calculation. Calculate the Variable: MonthInt plus 1. Output it to a new number variable called Next Month.
- Repeat steps 26 through 28. The only thing different will be that in the string builder seen in step 28’s screenshot, instead of Created Month, that variable will be Next Month.
- Put your cursor outside of the else statement that your last action was in. Add the action Set workflow variable. Create a new variable called Due Date Variable, as a date variable. Set the value of this to the value of the Due Date String. THIS is what converts it from a string that we strung together in the string builder… to an actual date.
- Add the Log to history list action, click the function button in there, and select the due date variable. This is optional, and you can add a lot more of these all over the place, which is very helpful for troubleshooting.
- Add the action called Set a field’s value. Set the Due Date field in this library to the value of the Due Date Variable.
It’s a pretty complex workflow, but we managed to fit it on one page (so far)
There are some more considerations that would need to be made also. What if it’s February? What if it’s December? Use the variables created at the beginning to find out these type of things, like Created Month is equal to 02.
To make it perfect, there would need to be conditions in the workflow to solve for all of those factors. I’m looking forward to your feedback!