Showing Recent Items in a SharePoint View
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:
(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.
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
You might find these two links helpful:
http://office.microsoft.com/en-us/sharepointtechnology/HA101215881033.aspx
http://office.microsoft.com/en-us/sharepointtechnology/HA011608661033.aspx?pid=CH011711541033
Nick.
nickgrattan
November 16, 2007 at 2:52 pm
Examples of common formulas:
http://office.microsoft.com/en-us/sharepointtechnology/HA101054791033.aspx
Keith
January 4, 2008 at 5:35 pm
[...] 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, [...]
Filtering views by Time and Date « Nick Grattan’s Techy Blog
April 24, 2008 at 1:59 pm
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
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
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
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
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
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
Excellent workaround, works perfectly
Mrmojorisin
May 5, 2009 at 5:25 am
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