Guest Author: PatCharles Iovanella

Mark forwarded me an issue from a SharePoint user in Australia. This user had hit a brick wall while working on a custom SPD workflow. I know how that feels, it happens to me as well. This is the original email:

Dear Mark,

As I progress with my SharePoint project I realize more and more that SharePoint gives you 95% functionality, but the 5 % missing is a real pain.  Anyway, wondered if you could help me with what I am trying to achieve?

Make a workflow (with SPD) which sends an email to the administrator when “Duration” of an event is greater than 60 minutes. This sounds easy, but as I found out, you cannot expose the “duration” column, and I believe that the duration is formatted as one day. So one hour is 1/24 day. But SPD does not allow you to enter a value of 0.0XXXX. It only allows whole numbers. Is there any workarounds for this?

The main issue is that if I use the rule “if duration > “time”…..” and time can only be expressed in whole numbers, and I cannot use any / or * syntax.   See snapshot

Many thanks,

Kind regards,
Paul , Melbourne, Australia.

My first thought was, there is always a workaround. The real solution is not to make the workaround a mountain of code, multiple if/else condition & actions or an end user nightmare.  This situation seemed simple enough so I didn’t want to over produce a resolution.

Here’s my solution:

Paul was on the right track with trying to use the Duration column in your Workflow condition. But what is the Duration? You know it’s 60 minutes because the time difference between your Date column and your End Date column is 60 minutes ( Date 1/14/10 5:00AM vs. End Date 1/14/10 6:00am = 60 minutes. But how does the workflow convert this difference when using the workflow Compare condition (If Duration is greater than X)?

What I did was use the Action ‘Build a Dynamic String’ to determine how the workflow sees the duration between the two Calendar columns (Date & End Date). And then I created a variable to hold that numerical output, which I add to the subject line of my email, so I can actually see the number.  I determined that workflow calculates a 60 minute duration between dates as 3600. 60 minutes equals 3600 seconds!  I tested this several times as a sanity check.  I tried different days of the week to make sure the 3600 was an accurate result.

Then I set my Workflow Condition as such ‘If Duration is greater than 3600’ .

OK, now that we know the value for the Duration, let’s see if the logic works! Paul was trying to create a workflow that alerts an administrator if someone posts a calendar entry when the meeting or reservation is greater than 60 minutes.  So I created my workflow and added the Duration variable as my subject line as a fast visual check.

Then I created some new calendar entries to test out the workflow.

The workflow sent emails only for the entries highlight above.

Short and sweet!

Guest Author: PatCharles Iovanella

PatCharles Iovanella works for major financial institution as the Americas SharePoint evangelist and subject matter expert delivering SharePoint business solutions to internal clients.  Pat has developed the firm’s global SharePoint Advice Center, newsletter, Self Help portal featuring custom training videos, use cases, how-to guides and new site owners toolkit. You can contact him at [email protected].