Fun With SharePoint Date Calculations

By James|11/27/2013|,

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.

Date Column Note

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.

InfoPath Date Calculations

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.

  • TaskFrequency - Choice column, choices Once, Weekly, Monthly, Quarterly, Bi-Annual and Annual"
  • TaskFrequencyStart - Date/Time column, date only
  • TFM - Number column, 0 decimal places
  • TFM2 - Number column, 0 decimal places
  • TFY - Number column, 0 decimal places
  • TFY2 - Number column, 0 decimal places

On the InfoPath form, I created the following action rules for the Task Frequency control:

Set Dates For ONCE

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 Dates For WEEKLY

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 Month For MONTHLY

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 Month For QUARTERLY

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 Month For BI-ANNUAL

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 Month For ANNUAL

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.

Set YEAR

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.

Update YEAR

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.

Set Dates

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")".

InfoPath Comments

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.

SharePoint Date Calculations

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:

  • NextWeek - Formula "=[Task Frequency Start]+7", return as date/time, date only
  • NextMonth - Formula "=DATE(YEAR([Task Frequency Start]),MONTH([Task Frequency Start])+1,DAY([Task Frequency Start]))+(1/24/60*480)", return as date/time, date only
  • NextQuarter - Formula "=DATE(YEAR([Task Frequency Start]),MONTH([Task Frequency Start])+3,DAY([Task Frequency Start]))+(1/24/60*480)", return as date/time, date only
  • NextBiAnnual - Formula "=DATE(YEAR([Task Frequency Start]),MONTH([Task Frequency Start])+6,DAY([Task Frequency Start]))+(1/24/60*480)", return as date/time, date only
  • NextAnnual - Formula "=DATE(YEAR([Task Frequency Start])+1,MONTH([Task Frequency Start]),DAY([Task Frequency Start]))+(1/24/60*480)", return as date/time, date only

SharePoint Comments

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.

Copyright 2011 - 2024 The Lazy IT Admin | All Rights Reserved
menu-circlecross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram