SharePoint list views filtered by date AND time
There are a huge number of articles out there discussing how to create (semi-) dynamic list views or filters based on some volatile property like current time. For instance: “Show me all documents that were created today” or “Show me all documents that were created more than 7 days ago” or “Show me all in tasks due in the next three days”. The solution to this is some combination of a list view filter, or calculated column, and the use of a property like [Today]. (Note: There are a couple of key caveats to do with this.)
A limitation of this, however, is that the time portion of a datetime is ignored. So when I wanted to “show all documents created in the last 10 minutes” - I couldn’t. There were a few workarounds - various combinations of calculated columns, using SharePoint Designer and so on were available - but I was determined to find a solution that didn’t require any of this!
Alas, I failed. I tried lots of things - Content Query web part, Data View Web Part, calculated columns, custom CAML - but whilst some of it worked, there was either always way too much plumbing required to get it in and it wasn’t particularly deployable between our Dev, Test and Prod environments.
The “least pain” and most deployable solution I arrived at is as follows. This describes what I did to achieve my specific requirements - you will need to adjust the parameters and logic to suit your needs. So I wanted a list view that showed all documents in a library that were created more than 10 minutes ago.
- Unfortunately* - create a calculated column on the field that contains the datetime you want to compare against, such as [Created] or [Modified], and specify the formula to include the time offset you need. So in my case, I wanted [Created] + 10 minutes, which looks like**
[Created] + 0.007
Call your column something memorable - e.g., CalculatedPlus10. You can verify this is doing what you want by adding it to a view and comparing its value against your reference field.
Now create a new view. It’s easiest if you start from an existing view, e.g., All Documents. Call it something like “CreatedPlus10”
Using Powershell***, I then set the Query for my new view, to use the new column. The trick here is that via the UI, you cannot tell a View to respect the time portion of the datetime field - that’s essentially what this query is doing:
<FieldRef Name=`”FileLeafRef`” /> <FieldRef Name=`”CreatedPlus10`” /><Value Type=`”DateTime`” IncludeTimeValue=`”True`”>
Note that the ” are escaped. This compares the value of your calculated column, and finds items that are Less Than now, including the time portion.
Then update the View and as if by magic, your view will now only show items that were created more than 10 minutes ago.
Your completed Powershell script might look a bit like this:
$web = get-spweb http://yourserver
$list = $web.Lists[“YourListName”]
$view = $list.Views[“CreatedPlus10”]
$view.Query = “
<FieldRef Name=`”FileLeafRef`” /> ” <FieldRef Name=`”CreatedPlus10`” /><Value Type=`”DateTime`” IncludeTimeValue=`”True`”>
And that should do it. I wrapped this whole thing up in a single script - i.e., create a calculated column, and then create your new view, and then set the list view query as above.
- I really didn’t want to have to resort to a calculated column. But there is no way in CAML - that I know of - to generate a dynamic property to compare against (ie. compare against Today + 10 minutes.) And an event receiver seemed like overkill.
** This on the basis of (1 / (24*60)) * 10 which is close enough to 0.007.
** A lot of the problems with these sorts of workarounds are usually “I don’t have access to SharePoint Designer.” If that’s true, then maybe you won’t have access to Powershell either. You could refactor this into some C# and then deploy it as an actual SharePoint feature, if you’re in a particularly locked down environment.