Any solution to the Today Calculated Column problem is SharePoint?

Question

I would like to be able to use today's date in a calculated column in a SharePoint list to, for example, determine whether a task is overdue. There is a well-documented trick that involves creating a dummy column named "Today," using it in a formula, and then deleting it, thereby "tricking" SharePoint into using the Today function.

The problem is that this method does not work reliably -- the calculation is not dynamic; it is only made when the item is saved, and therefore the Today "column" effectively becomes the Modified Date. (This is probably why SharePoint won't let you use the Today function in a straight-forward way.)

Has anyone found a solution that works? I know I can use javascript to get the actual date on the client side and display colors, flags, whatever, but I am looking for a "server side" solution.

---

For reference, the Today column trick and its problems are described fairly well at these two posts and associated comments: http://blogs.msdn.com/cjohnson/archive/2006/03/16/552314.aspx and http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

Answer 1

There simply isn't a work around for this. As the values for the list are stored in the database and returned "as is" to other featurs such as the search crawler, a dynamic field cannot be created.

It is possible to create a custom field that will display the value using todays date in its calculation.

Answer 2

In addition to Christophe's (PathToSharePoint)'s article this also covers the Today trick and why it doesn't work

The Truth about using Today in calculated columns

There are a number of fudges, probably the best one is Dessie's console app (mentioned above by MNM)

Dynamically updating a SharePoint calculated column containing a Today reference

Its good but its not perfect, for example you may have to worry about different timezones.

Before going down this route you should ask yourself if you really, really need to do this. For example :-

• If you want a countdown (days overdue/days left to complete a task) then you can use SPD and a XLST Data View web part

• If you want a view to show overdue items or items created in the last X days ec then you can use [Today] in a views filter 2

Answer 3

If you create a Today column it needs to be updated. You can do that with either a timer job or by placing a jquery script on a page that is hit by the user. The script could call SPServices.SPUpdateMultipleListItems to do the update. Pass a CAML clause so that you only update the list items where the Today value needs to be updated, e.g. once per day.