How to keep SharePoint calculated columns up-to-date

An oldie but a goodie I used today._____________________________________________

The major issue related with SharePoint calculated columns is the fact that they are not dynamically updated. In  other words, they will be updated each time you create or edit list item, but before the next item change they will not be recalculated ever again. In most of the cases this is good enough, but if your calculated column contains some date and time value (e.g. today @ 8:00 AM) or if it summarizes some numbers on a daily basis, the value will become obsolete the very next day after the last item change.

Although the issue looks bad on the first sight, there is a simple solution. You can create a simple PowerShell script and schedule it to run on a daily basis.

$spWeb = Get-SPWeb -Identity http://server/subsite
$spList = $spWeb.Lists["Display_list_name"]
$spList.Items | ForEach-Object { $_.Update() }

Let’s see what’s in the script.
First, we need to get a reference to the web site containing our list with calculated fields. Note here that you need to define full path including all subsites.
In the second step, a reference to the list object will be retrieved. Using above defined syntax, you’ll have to use list display name in order to get list object.
Last, but not least, we’ll iterate over all list items and call the Update method.

Now when we have the script, there is only one more thing we need to do in order to get this working – we need to schedule our script to run every day at 00:01, so the values get up-to-date for that day. For that you should use Task Scheduler. If your data changes more often, you can schedule script to run inside shorter periods of time.

PowerShell scripts containing SharePoint related commands are executed using SharePoint 2010 Management Shell which is located here: “C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe”. So, in Task Scheduler you’ll have to reference SharePoint 2010 Management Shell and to provide script as the argument.