CalendarWhile working on a client request recently, I came across a way to easily work with date and time values in a SharePoint Designer 2010 workflow.  I was able to solve this using only SharePoint Designer.  I reviewed other solutions, and I will give links to a couple others in case they are needed.  This worked for me, and I didn’t see anyone else with this exact solution so I thought I would share for the greater good. 

In my case, I was writing a workflow for an equipment reservation site.  Emails were to be sent on item create, as well as at 8am of the start date, 8am of the end date, and at 8am of an overdue date.  To be able to calculate 8am of the start date, I obviously need to get just the date of the start date field, then add 8 hours to it.  Sounds simple enough.  SharePoint Designer provides the ability to return the date values in various formats:

SPD Workflow variable date format

  • As String:    2/2/2014 12:00:00 AM
  • ISO Formatted:    2014-02-02T06:00:00Z (Zulu – GMT)
  • Long Date:    Sunday, February 02, 2014
  • Long Time:    12:00:00 AM
  • Short Date:    2/2/2014
  • Short Time:    12:00 AM

Ok cool, I just need to get that format into a variable.  Houston we have a problem:

SPD Workflow variable date format grayed out

So obviously we need another way.  For my requirement, I didn’t need the date only, I just needed to use it to calculate 8am of that day.  You could use a calculated column to do it, which is fine, then call in the workflow.  But I didn’t feel like making another column.  I used the “set time portion of date field” and “add time” workflow actions.  Here’s what the actions look like:

SPD Workflow variable date format

First I needed 5 date/time variables:

  • PickUpDateOnly – date only for start date
  • ReturnDateOnly – date only for end date
  • PickUpEmailTime – start date @8am
  • ReturnEmailTime – due date @8am
  • OverEmailTime – 8am 1 day after ReturnEmailTime

So we start with the Start Date and End Date fields, which are the provided “date and time” fields.  When dumped “as string”, they show as 2/2/2014 10:17:00 AM.  The user could specify the time period for which they needed the equipment, which could be at any point of the day, like from noon-5pm.  I wanted the pickup email reminder to go out at 8am the morning the equipment was to be picked up, then 8am the morning of the day it was due to be returned. 

Using the set time of date/time field, I set the hours in effect midnight 00:00.  This in effect gave me the day only.  Then, I just add 8 hours with the Add time workflow action and output that to a variable.  So I fire the fire the first email on item create, then pause using the Pause action until the first PickUpEmailTime (which is 8am of the start date), then fire the email. 

Other Ways

As I mentioned, there are other ways of doing this.  One method is when you create your variables, create them as STRING, not Date/Time.  Then you are able to set the variable to ISOFormatted, and do string extraction to get the date.  Check out this link for an example.  You could also create a calculated column in the list, and set it to only return the date portion of the Start Date field.  Refer to MSDN for examples.

For more information on C5 Insight or this blog entry, please Contact Us.