Search This Blog

Saturday, May 7, 2011

How to Add and Subtract Hours and Minutes from Date and Time Fields in SharePoint 2010 Lists


I recently needed to write a SharePoint Designer workflow to send a reminder email 12-hours before the end of a multiple-day event. It’s easy to use the Pause Until Date activity to pause until the necessary time, but it’s not so obvious how to calculate the date and time ithe workflow needs to pause until.

The easiest way to calculate the Pause Until date and time was to create it as a calculated column in SharePoint. Note: although the screenshots in this post are from SharePoint 2010, the principles are the same in SharePoint 2007.

In the Name and Type section, for the name of the column name I used DateTime for PauseUntil. I selected the Calculated (calculation based on other columns) data type.


In the Additional Column Settings section, I temporarily entered a formula of =[End Time]-1, selected Date and Time as the data type, and selected Date & Time as the format.


You’ll notice in the screenshot here that there are three different End Times and the calculated column subtracts exactly 24 hours for the =[End Time] –1 formula.




If we divide 1 day by 24 hours and divide 24 hours by 60 minutes, we get 1/24/60 = 0.00069444444. So, if you change the formula to =[End Time]-1/24/60




You’ll notice that the dates and times in the Date for PauseUntil column are exactly one minute before the dates and times in the End Time column.




What this means, is that to add or subtract a certain number of minutes from a date and time field, we just need to multiply 1/24/60 by the number of minutes we want to add or subtract. In my problem, I needed to subtract 12 hours which is 12*60 or 720 minutes. Let’s see if that works.




Yep, all the calculated times are exactly twelve hours before the end time; and if you’re wondering, crossing midnight times doesn’t cause any problems for these calculations.




So, if you need to add or subtract a certain number of hours or minutes in a calculated column in SharePoint, you can simply multiply 1/24/60 by the number of minutes you need to add or subtract to achieve any value you need.

2 comments:

  1. It is not working for AM timings..
    Deva
    thanks

    ReplyDelete
  2. I've been lurking around here for a while but needed to comment. This is rather fascinating, do another like this one!

    my web site: homemade dog shampoo

    ReplyDelete