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

My advice is to create your on field that does this calculation for you and then reference it in your SharePoint list. Not a simple implementation but it would work.

Answer 2

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 3

I have been looking for a solution either, still no luck.. The Today column trick has the limitation of not being dynamic.

I do have one suggestion though, why don't we create a timer job that will update a certain a certain column with the current date every day at 12 AM. I know some of you all might think it an over head. Just my suggestion :D!!

Answer 4

and here's another workaround.. Similar to what i mentioned above..

Workaround

Answer 5

I came up with a very rough, but working solution to this problem without having to do any coding. I'll explain both how i made the today column and how i worked that in to an overdue column, becuase that column was a pain to find out how to do as well.

First, I made a column named "today" (gasp!). Next I made a column named "Days Overdue". I then opened up sharepoint designer and created a new workflow. I set it to run every time an item is edited/updated (keep in mind I turned off versioning for this list, otherwise I would have had to resort to coding to avoid a bunch of useless data building up on our server). I set the actions to simply store the modified date in a workflow variable, then change the value of the today column to that variable. although the modified column is a date/time and my today column is just a date, it transfers just fine. I then set the workflow to pause for 2 hours. you can set this to whatever amount of time you want obviously, it will just change the latest possible time for your today column to update, i.e. 2AM in my case.

on to the days overdue column. this is the code for that guy -

=IF([Due Date]>Today,"None",IF([Date Closed]=0,Today-[Due Date],IF([Due Date]>[Date Closed],"None",IF(Today>=[Date Closed],[Date Closed]-[Due Date],IF([Due Date]<Today,Today-[Due Date])))))

This shows the days overdue in number form in days, or if its not overdue, it shows "None". You can use either a number format or a string format, but NOT A DATE FORMAT. Well, I hope this helps anyone who is running into this problem and doesn't want to have to delve into coding.

EDIT: I forgot to say that in the code above for the days overdue column, I put in that if today is past the date closed, to use the date closed minus the due date instead of today minus due date, to ensure that the calculation doesnt keep occurring after an item has been closed. you probably would have noticed that in the code, but i felt i should point it out just in case.

EDIT 2: The code I had in before my 2nd edit for my calculated column didn't calculate the days overdue properly after an issue had been marked "closed." I put in the updated code. The last part of the code doesn't make sense, as it is the same logic as the beginning, but it worked so I didn't want to take any chances! :)

Peace.

Answer 6

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 7

I had the same problem and sort of got away by injecting some JavaScript as described in this blog post http://www.mindovercode.com/2010/08/17/jquery-helper-for-sharepoints-today/

Answer 8

Hi, This link is the solution for you. It's work for me. Try it.

http://blog.pathtosharepoint.com/2008/08/25/a-countdown-for-tasks-lists/#comment-5251