RSS Feed Is this your first time here? Get regular updates

The … ehem… Truth about using [Today] in SharePoint Filters

Date:November 25th, 2008 Author: Tags: , ,
Category: Calculated Columns, Filter, SharePoint Ideas Comments:12 ;

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

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"

"Filter value is not in a supported date format"


Try taking out the spaces…


Created is greater than [Today]-7

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: , ,

Did you enjoy this post? Get the next one by RSS or by email

12 Responses to “The … ehem… Truth about using [Today] in SharePoint Filters”

  1. Chris Shepherd says:

    Thanks for that – a space was riving me mad!

  2. tfrink says:

    thank you. i was cursing the stupidity of not being able to do this.

  3. Richard says:

    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

  4. Ryan says:

    @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

  5. ChenChen says:

    How can I use this to display items due in the next 7 days but not overdue?

  6. Ryan says:

    [Status] is not equal to “Completed”
    AND
    [Due Date] is less than [Today]
    AND
    [Due Date] is greater than [Today]-7

  7. cyborg says:

    thnx a ton !!!

    This post helped me a lot :)

  8. Sarah says:

    I’m trying to figure out a way to use this filter “trick” to have a calculated column that shows the number of days open (Created – Today’s date) that will update every day. I used the much talked about “today” then delete “today” column trick, but that is obviously not updating on a daily basis.

    Any ideas? I don’t want to look at a due date or a completed date – just need it to calculate age of item.

    Thanks!

  9. Ryan says:

    @Sarah. Yeah that [Today] trick in calc columns doesn’t work and there is a link in the post above that explains why.

    There is no easy way to do a count-down or count-up (as in your case) between two dates and you need to go the extra mile.

    This post gives you some options – http://blog.pentalogic.net/2011/05/how-to-create-a-countdown-in-a-sharepoint-list/

  10. Tim says:

    Hello there

    I’m struggling to ascertain from my research online whether or not this has been addressed by Microsoft. I use the ‘Now()’ function in Excel for a list I need to move over to SharePoint (we’re running 2007 on MOSS). The list needs to do the following:

    Compares a ‘deadline date’ (a manually entered date when a new list item is created) with today’s date (i.e. the day the list is accessed) that gives a value of ‘Red’ if today’s date is at least one day beyond the deadline date, ‘Amber’ if today’s date is equal or within 18 months of the deadline date and ‘Green’ if the deadline date is beyond 18 months of today’s date.

    ..and, you’ve guessed it – ‘today’s’ date must reflect the date the list is accessed!

    The colours aren’t actually important, I just need a formula that displays the name of the colour. Whilst your filtering method above could achieve this, it wouldn’t be as effective as each list item being labelled with the appropriate R, A, G status (should we need to export the list and share outside of SharePoint). Workflows aren’t required, we simply need to show the R, A, G status of the items.

    Many thanks

    Tim

  11. Ryan says:

    @Tim – Today works in filters, but never has worked in Calculated Columns (aka Formula) and is never likely to – see link on “Limitations of Today trick” above.

    If you want to do something like conditional formatting depending on if something is overdue then you have to either
    a) Do it using SharePoint Designer http://blogs.msdn.com/b/sharepointdesigner/archive/2008/02/25/filtering-and-formatting-with-date-values.aspx
    b) Do it usign javascript http://spjsblog.com/2011/11/29/compare-date-with-today-status-indicator-in-list-view/

    or c) Buys something like our Highligher tool which makes this and more really easy!

    http://www.pentalogic.net/sharepoint-products/highlighter/h-manual/h-example-uses/h-eu-overdue

  12. Tim says:

    Thanks for the very prompt response, Ryan!

    I also realise that you’ve had to repeat some of your previous advice so thanks for that too.

Leave a Reply

Anti-Spam Quiz: