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…

  1. Add the action: Set Workflow Variable
    221-image_4_3CF5D55A.png
  2. Click the blue words workflow variable, and click Create a new variable…
  3. 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.
    221-SNAGHTML71b2cc_1_3CF5D55A.png
  4. 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.
    221-SNAGHTML7bba7a_1_3CF5D55A.png
  5. 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.
  6. 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.
    SNAGHTML80a6db
  7. 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:
    221-image_f973fcd5-7381-4ccd-859f-1ccd1f14974a_3CF5D55A.png
  8. 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.
  9. The syntax of the ISO formatted date is as follows:
    2014-04-17T15:56:37Z
    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.
  10. 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.
  11. 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.
    221-image_34366856-99a8-4d7f-b3d9-c1f7f775ab7c_3CF5D55A.png
  12. 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.
  13. 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.
  14. 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.
  15. 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.
  16. 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.
  17. Click on the blue words workflow variable, and click Create new variable… Name it MonthInt, and the type is Integer.  Click OK.
    221-SNAGHTML57b4324_3CF5D55A.png
  18. 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.
    221-SNAGHTML57dce91_3CF5D55A.png
  19. 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:
    221-image_d6cbfd7b-d588-4f67-976d-d11a6b93eca9_3CF5D55A.png
  20. 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.
  21. Click the first value, and select the Document type field from the current item data source.  Click the second value, and choose Policy.
  22. 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.
  23. 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.
    221-image_b893eb09-a3d4-4abf-881d-99a7acbff1e0_3CF5D55A.png
  24. Outside of the condition you just added, after it, add the action Do Calculation.
    221-image_7b6f8858-6066-4d7b-8d2d-daf7b8f2ab3b_280452E7.png
  25. 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
  26. 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:
    221-image_f6ff0ed1-8241-4984-9783-14ea90be3be7_280452E7.png
  27. 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.
  28. 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.
  29. Click the blue word value.  Click the little ellipsis (and not the fx button).
    221-image_5b5dbed5-d8c6-43c9-b94f-fffc7bedfda6_280452E7.png
  30. 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.
    221-SNAGHTML5deced1_280452E7.png
  31. 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 CalculationCalculate the Variable: MonthInt plus 1.  Output it to a new number variable called Next Month.
  32. 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.
    221-image_c5b3e7c3-bc66-40e6-a715-e0ec0f8a004d_280452E7.png
  33. 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.
  34. 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.
  35. 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)

221-image_8a0d08f2-4e34-40f6-82ee-6463992ec83b_280452E7.png

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!

Technorati Tags: ,,

6 comments

  • Hello Laura, thank you for your article, but some time ago I had the same problem. After little research I made custom workflow action for getting date in correct format. You can find complete open source solution on
    https://spworkflowactions.codeplex.com/
    Documentation available on
    http://plumsail.com/workflow-actions-pack/docs/string-processing-workflow-actions/#FormatDate

    Like

  • Thanks so much Roman, that’s awesome!

    Like

  • Hi Laure, I spoke to you at SPTECHCON Austin, I have a training list with 24 columns, each column has a date. The Date in these columns turns red if the date is less than current date -365. how do I build a workflow to loop thru so many different dates and figure out if the dates is too old and send an email stating the old date? is such an workflow even possible with sharepoint 2010?

    Like

  • Joe,
    Hi, have you tried using an information management policy? Those don’t loop through multiple items, so you’d get an email for each item, but they’re based on a date field + some number. Unfortunately, you’d have to end up creating an extra field for each of your dates, I think, and you’d have to use a workflow to set the extra hidden copy of each one to that date minus 365. THEN, you could create an information management policy that goes through each of those and sends an email like you said. (multiple retention stages in the policy)

    Like

  • Hi
    Thank you for putting this detail and useful method. one issue is coercion problem will stop workflow from running ..as string is going to be converted to integer .. like 04 to 4 …

    Like

  • Of course the ISO date method potentially fails if you live in a different time zone to GMT. When I do the above and log the results I get
    10/02/2017 10:23 AM Comment No presence information 2017-02-09T23:23:55Z
    The local date here is 10/02/2017 but extracting the day from the ISO date gives me yesterdays date. Any ideas?

    Like

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s