Working with date calculations in SharePoint can be about as much fun as stabbing yourself in the eye with a fork. With that said, I wanted to share some tips and workarounds that I came across while working on a recent project that required date manipulation.
You can configure a column as date/time with the option to only show the time. However, while you will only SEE a date, SharePoint will still assign a time to the column. The time assigned will be midnight 00:00. This can make life interesting if you're dealing with customers from multiple geographical locations, where midnight in one location may be 9 PM or 3 AM somewhere else. This can make life painful if you want everyone to see the same day.
For the project, I needed to allow users to select a frequency and then automatically figure out the next occurrence of said frequency. The frequencies are: Once, Weekly, Monthly, Quarterly, Bi-Annual and Annual. I chose to use InfoPath rules to perform the date math, although SharePoint calculated fields could also be used. In fact, I ended up using calculated fields to accomodate additional workflow date math processes for the project. More on that later in the "SharePoint Date Calculations" section.
I created the following columns in a custom list to support the date calculations.
On the InfoPath form, I created the following action rules for the Task Frequency control:
Set condition to "Task Frequency" = "Once". Add an action to "Set a field's value" for the TaskFrequencyStart field. Set the value to function "now()". Check "Don't run remaining rules if the condition of this rule is met".
Set condition to "Task Frequency" = "Weekly". Add an action to 'Set a field's value" for the TaskFrequencyStart field. Set the value to function "addDays(now(), 7)". Check "Don't run remaining rules if the condition of this rule is met".
Set condition to "Task Frequency" = "Monthly". Add an action to "Set a field's value" for the TFM2 field. Set the value to function "number(substring(now(), 6, 2)) + 1". Add an action to "Set a field's value" for the TFM field. Set the value to field TFM2.
Set condition to "Task Frequency" = "Quarterly". Add an action to 'Set a field's value" for the TFM2 field. Set the value to function "number(substring(now(), 6, 2)) + 3". Add an action to "Set a field's value" for the TFM field. Set the value to field TFM2.
Set condition to "Task Frequency" = "Bi-Annual". Add an action to 'Set a field's value" for the TFM2 field. Set the value to function "number(substring(now(), 6, 2)) + 6". Add an action to "Set a field's value" for the TFM field. Set the value to field TFM2.
Set condition to "Task Frequency" = "Annual". Add an action to 'Set a field's value" for the TFM2 field. Set the value to function "number(substring(now(), 6, 2)) + 12". Add an action to "Set a field's value" for the TFM field. Set the value to field TFM2.
Leave condition as "None - Rule runs when field changes". Add an action to 'Set a field's value" for the TFY2 field. Set the value to function "number(substring(now(), 1, 4))". Add an action to "Set a field's value" for the TFY field. Set the value to field TFY2.
Set condition to "TFM2 > 12". Add an action to 'Set a field's value" for the TFM field. Set the value to field TFM2 - 12. Add an action to "Set a field's value" for the TFY field. Set the value to field TFY2 + 1.
Leave condition as "None - Rule runs when field changes". Add an action to 'Set a field's value" for the TaskFrequencyStart field. Set the value to function "concat(TFY, "-", substring("00", 1, 2 - string-length(string(TFM))), TFM, "-", substring(now(), 9, 2), "T08:00:00")".
Let's break down the rules. A date/time format in InfoPath will be seen as YYYY-MM-DDTHH:MM:SS, or as an example "2013-11-26T12:23:30". For a weekly calculation, the adddays() function was used to just add 7 days. When adding months to the current date, the substring and now functions were used to figure out the current month and add the required number of additional months. Temporary fields (TFM/TFM2/TFY/TFY2) were used to hold and manipulate the month and year values. Once the desired date was calculated, the concat() function was used to build the date/time value from the temporary fields and populate the control.
You can also work with dates using SharePoint calculated fields. To perform the same date math that was done above in InfoPath, create the following calculated columns in your custom list:
Keep in mind that calculated columns only update when the list item is created or updated. Also, you may be wondering what the "+(1/24/60*480)" bit is at the end of each formula. As noted in the beginning of this article, if you set a date/time column to "date only" SharePoint will set the time portion to midnight. I wanted to add eight (8) hours to the time portion and make it 0800. If you add an integer to a date/time value, days will be added. So, to add hours we add a fraction of a day! So, the "+(1/24/60*480)" equates to 1 day divided by 24 hours in a day divided by 60 minutes in an hour multipled by 480 minutes (60 minutes * 8 hours). The result? 0.3333333333333333 days. Looks bizarre, but it works! Some good information regarding SharePoint calculated columns can be found here.
