Back in the day of Windows SharePoint Server 2 and SharePoint Server 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](http://blog.pentalogic.net/wp-content/uploads/2008/11/today-in-wss2-fitler.png "today-in-wss2-fitler")
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…
The Truth about using [Today] in Filters
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 -
"Filter value is not in a supported date format"
Try taking out the spaces…
![Created is greater than [Today]-7](http://blog.pentalogic.net/wp-content/uploads/2008/11/today-in-wss3-filter.gif "today-in-wss3-filter")
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.
|
Free SharePoint Calculated Column Cheat Sheet |
| a handy 3 page reference for calculated column functions and formulas. |
 |
Tags: Formula, SharePoint, Today
Thanks for that – a space was riving me mad!
thank you. i was cursing the stupidity of not being able to do this.
Hello,
In the filter view I can filter by [Today] which apparently uses just the date for today. How can I do the same thing, but include the current time? So I’d like to filter against Date/Time.
Thanks,
Richard
@Richard – afraid there is no way AFAIK to do this – you don’t have a [Now] function like you do in Excel.
What are you looking to do with this? Blatant plug but you can use our Highlighter product to do things like highlight in red anything that should have been completed by “Now” and this can include the current time – http://www.pentalogic.net/sharepoint-products/highlighter
How can I use this to display items due in the next 7 days but not overdue?
[Status] is not equal to “Completed”
AND
[Due Date] is less than [Today]
AND
[Due Date] is greater than [Today]-7
thnx a ton !!!
This post helped me a lot