Nick Grattan’s SharePoint Blog

About Microsoft SharePoint and some .NET

Showing Recent Items in a SharePoint View

with 26 comments

Update 16 October 2000: Thanks for all the comments below. Based on these, here are some corrections:

1. You can use “[Today]-5″ directly in the view so avoiding adding new fields etc. to the list or library.Why didn’t I? Well, if you add spaces around the “+” so the expression reads “[Today] – 5″ you will receive an error back from SharePoint: “Filter value is not in a supported date format”. I normally add spaces as I think it makes it more readable. Remove the spaces, and the view will update. The result should look like:

filter.PNG

(Thanks to Joe at Cork County Council for helping discover the issue with spaces)

2. When doing date calculations you can simply add or remove a number of days rather than the long-winded formula used below.

__________________________________________

When lists have many items it’s useful to be able to display only those items created or perhaps modified in the last number of days. Since expressions cannot be added to filters in views a calculated column needs to be added to track the age of the item.

You might be tempted to use [Today] in the site column expression to calculate the number of days since the item was created. You will be able to save the site column, but when you add the column to the list the following error is displayed:

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

This is because the values are calculated when the items is saved, and not when the item is queried.

The way around this is to create a calculated site column, called for example “DaysOld”, with the following expression:

=DATE(YEAR([Created]),MONTH([Created]),DAY([Created])+5)

Where “5″ is the number of days to be used in the filter (e.g. “show items created in the last five days”). This expression will calculate the date five days on from when the item was created. It should look like:

You should now add this site column to the list which needs to be filtered. A view on that list can now have a filter expression that uses this calculated field:

“[Today]” can be used in the filter expression since its value is known when the query is executed, and so is determinate.

About these ads

Written by Nick Grattan

September 6, 2007 at 6:10 pm

26 Responses

Subscribe to comments with RSS.

  1. hi

    it’s quite helpful. I am currently struggling with the calculation fields.
    could you tell me, where is the function and usage explanation which can be used in moss 2007? it’s really hard to find.

    from your blog, I know functions like DAY, YEAR, MONTH… are working on MOSS, but how about others? forexample, DATEDIFF? I tried DATEDIFF(“d”,[currentdatecolumn], [plandatecolumn]) but MOSS says it’s invalid.
    :(

    thanks for help!

    Bruce

    September 19, 2007 at 9:26 am

  2. Keith

    January 4, 2008 at 5:35 pm

  3. [...] Time and Date Published April 24, 2008 SharePoint , SharePoint PowerUser An earlier blog entry describes how to filter List and Document views by date. However, the [Today] function, by default, [...]

  4. I tried both
    [Modified]+[AgingDays]
    and
    DATE(YEAR([Modified]),MONTH([Modified]),DAY([Modified]))+[AgingDays])
    to calculate a calculated column DisplayUntil. I am observing that the DisplayUntil gets calculated correctly for a moment and then gets set to a very old date. Something to do with cascading updates perhaps. Has anyone tried this with modified date rather than created date?

    Ganesh

    May 23, 2008 at 12:58 am

  5. In site columns, the formula and everything is just as I need it. I click ok. When I insert the column to my list, the formula is empty and therefore will not be inserted. What am I doing wrong?

    Marie

    June 5, 2008 at 8:16 pm

  6. Hi, i’m trying to subtract to dated and store the result in a number.
    The problem comes when one of the dates is empty…how can i workaround this?

    Tiago

    September 16, 2008 at 5:03 pm

  7. Hi…

    But you could just create a filter in the view with “Modified is Greater than [Today[-5″

    Works for me.

    But what I really want is a way to filter items that I have not viewed before. SharePoint has that “!new” icon in green… how to I filter on that… and does it have a “modified” icon?

    Nic.

    Nic

    October 14, 2008 at 3:42 pm

  8. I just am using the view filter to see numbers of days old. I filter by the Created field <= [TODAY]-X (X=equals the numbers days or older). I created views for 10, 30 or 90 days or older.

    Terry

    October 16, 2008 at 11:29 pm

  9. If you want to hear a reader’s feedback :) , I rate this post for 4/5. Decent info, but I have to go to that damn msn to find the missed bits. Thanks, anyway!

    Heartburn Home Remedy

    April 15, 2009 at 12:38 pm

  10. Excellent workaround, works perfectly

    Mrmojorisin

    May 5, 2009 at 5:25 am

  11. I’m trying to create a view to show previous day’s activity. I tried using the formula above: “Created is Greater than [Today]+1″. It’s giving me items created today AND yesterday. Can you help me figure out what I’m doing wrong?

    Alison

    October 13, 2009 at 3:27 pm

  12. The spaces thing caught me too! I mean, white spaces always get ignored, right? ;)

    Related – if you want to filter on the calendar month rather than a rolling 30 days then I’ve blogged about how you can do this

    http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

    Ryan

    November 27, 2009 at 12:07 pm

  13. Created is greater than or equal to:
    [Today]-14

    I did a quick test on older tickets created yesterday, and this works, as the Created date never changes.

    Eric Schrader

    January 18, 2010 at 11:44 pm

  14. I have a column which is single line of text. The values will be like
    24/Jan
    12/Feb

    I want to create a view and i want to show the data which will be of of today. In filter expression i selected
    DAY
    IS EQUAL TO
    =TEXT([TODAY],”D/MMM”)

    It didnt helped me.
    Thanks in advance.

    Vamsi

    January 24, 2010 at 6:59 pm

  15. Great post, but trying to create a View for “New This Week” which no matter what day (Mon-Fri) it will show only Created items for this week.

    Have tried calculated field approach and then filter by it, but cant get my head round the logic.

    Has anyone done or advice how to do this?

    Wayne

    February 24, 2011 at 4:10 pm

  16. What if I need it to go off of the time, too? Can SharePoint do that? I.e. give me a view where Modified is greater than MM/DD/YYYY XX:XX PM? Using just MM/DD/YYYY (with actual numbers) works, but not when I add the time.

    Tom

    August 20, 2011 at 1:40 am

  17. Hi, is it possible to filter on only the most recent entry in one view, and then all older entries in another view?

    I want to show the most recent status update regardless if it was in the last 7 days, or 60 days.

    Thanks!

    Tim

    November 17, 2011 at 5:02 pm

  18. Awesome, thanks!

    user nanme

    March 20, 2012 at 2:23 pm

  19. There is a webpart that uses the OOB controls from SharePoint 2007 & 2010 to display the recent documents:
    http://www.fiechter.eu/blog/Lists/Posts/Post.aspx?ID=19&mobile=0

    Adrian Fiechter

    April 9, 2012 at 10:53 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 50 other followers

%d bloggers like this: