Back in the day of WSS 2 and SPS 2003 you couldn’t use [Today] in Filters.

For example if you tried to setup a filter like this to show items added in the last 7 days

Created is greater than [Today] – 7

You would receive an error “Filter Value is not a valid date” when clicking OK

This lead to all sorts of inventive work arounds including calculated columns to add 7 days onto the Created date and the trick to use [Today] in calculated columns.

After kicking up a bit of a storm with some ill considered remarks in a post explaining the limitations of the [Today] trick I realised I had some of my own old posts that needed updating. Live by the sword…

<tongue-in-cheek> The Truth about using [Today] in Filters </tongue-in-cheek>

In WSS 2 / SPS 2003 you could not use a filter containing a [Today] formula as shown above.

In WSS 3 / MOSS 2007 you can. If you try

Created is greater than [Today] – 7

you see a similar error -

Created is greater than [Today]-7

Yup, that easy.

Just to make clear – you can use other fields. For example to to show all the tasks due in the next 7 days or overdue.

Due Date is less than [Today]+7 AND Status is not equal to Completed

If you are writing CAML queries then see this post about using the OffsetDays attribute.

[EDIT] As it’s been pointed out to me the tone of this article was off from what I intended – the intention is to make clear that this well known and oft mentioned trick does not work as you might expect and to offer alternatives.

If you try to create a calculated column with a formula containing [Today] or [Me] SharePoint gives the error :

“Calculated columns cannot contain volatile functions like Today and Me”

There are a few reasons you might want to do this. One would be to display how many days left you have to complete something, the other is to only show things added in the last X days or due in the next X days.

There are many pages mentioning this workaround :-

1) Create a dummy column called “Today”, then add your calculated column using [Today] in the formula, finally delete the dummy column. If you need to edit the formula then you have to go back to the start and add the dummy column again.

2) Optionally you can create a calculated column called “My Today” and set its formula to =[Today] using the trick above. Reference [My Today] in your other calculated columns. Thus you reference [My Today] which in turn references [Today]. Then you will not have to add the dummy column back in every time you want to edit formulae.

You then pat your self on the back and head home after another successful day of harassing SharePoint to do what you want, Hurrah!

But the next day you take a look at your list and its wrong. The formula has not updated and its still showing yesterdays value. DOH! SharePoint has made a fool of you after all.

Turns around there is actually a good reason for SharePoint not allowing volatile functions like [Today] or [Me] in calculated columns.

“Calculated fields don’t update themselves until item itself is updated.”

Read that again.

The value of a calculated column is only updated when item is edited and in the mean time you will see incorrect calculations every time you look. In fact, for most uses, this trick don’t give you anything more than just referencing the [Modifed] column.

One workaround to the workaround problem is to write a console app to find all these lists that use the [Today] trick and update them automatically once per day. But this workaround to the workaround problem may not be without its own problems that need to be worked around….

Depending on your need there may be a simpler way – turn the problem on its head!

In the example of using a calculated column to filter out items due in x days or overdue SharePoint will accept [Today] as part of the Filter but will not as part of a calculated column – so give it what it wants

[EDIT- WSS 3 / MOSS 2007 now accepts this]

• Use a filter of [Created] is greater than [Today]-7

(Note – its essential to have no spaces; [Today] – 7 will not work but [Today]-7 does.

WSS 2 / SPS 2003

• Create a column called “Filter 7 Days” and formula as =Created+7
• Then use a filter of “Filter 7 Days” is less than [Today]

See this page for more details – http://blog.pentalogic.net/2008/10/advanced-sharepoint-view-and-filters/

So the calculated column is not dynamic (it will never need updating unless the record itself changes) and SharePoint is happy – but the filter is dynamic and you are happy!

If you want a countdown in your view (i.e. Days left to complete a task) then take a look at this post – http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/

Shameless plug – check this product out if you want “Alert Me” type emails when a task is due soon or overdue.