In my SharePoint 2013 Power User Fast Track class last week, a student asked if she could make a column display today’s date in SharePoint 2013.
This seemed simple enough using a calculated column, but then I discovered there is a restriction on calculated columns that forbids the use of the [Today] and [me] functions. There are whole discussions on why this is the case, similar to this one.
The simple explanation is that calculated columns don’t update until the individual item is updated.
So what is the solution to this problem? Well, the answer to that relies more on what your end goal really is. If you want to be able to see items within a certain date range, then a view can work around this by using a simple filter like “deadline is less than [Today] +3”. This will show everything with a deadline of 3 days from now or less.
After some back and forth, I learned that the reason for the [Today] column is to remind users about items that are coming due. A way to accomplish this in 2013 is to use workflows. With SharePoint 2013 and Office 365’s SharePoint Online, we now have the ability to make custom workflows that loop. Combining this capability with pausing workflows allows us to update or check our records every 24 hours.
Workflows in 2013 CAN set a variable to Today’s date and then compare that to the deadline value. Or today’s date can be written back to a column in the item every 24 hours.
There are two things to consider at this point though. One is that you need to set escape conditions for your loop so that SharePoint doesn’t run an infinite loop. The best option is probably to set the loop to check if the task, document, or item has a “completed” condition.
The other consideration is what kind of load this will generate on your server. While a dozen records being updated by workflows all at the same time will not create much of a drag, having thousands of records updated at the exact same time may create enough drag to bog your farm down. You will want to set a large number of operations to occur very early in the morning (like 0200) or stagger the actions so some occur every half hour. I'll leave that to your imagination unless specifically requested in the comments.
Other than what we can do in SharePoint, this once again emphasizes what my SharePoint mentor taught me when I started: Sometimes what you are trying to do is not as important as the end result you want to accomplish.
While we can’t create a calculated column that keeps today’s date in it, there are new ways of doing things that take our journey in a different direction but get to the same result.