Showing Recent Items in a SharePoint View
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