Share via

I need help creating a list view filter based on time of day

Question

Tuesday, January 24, 2012 8:37 PM

I need a view of a list to filter the values based on the time of day.  For example, I have a column called Start Time in date/time format.  I need the filter to list all values between between 1/24/2012 8:00 AM and 1/25/2012 8:00 AM.  Using the filter value [TODAY] returns all items for either day regardless of the time.  Is there a way to specify a time range filter?  Something like:

greater than or equal to "[TODAY] plus 8 hours" and less than or equal to "[TODAY]+1 plus 8 hours"

Matt H.

All replies (12)

Friday, January 27, 2012 12:26 PM âś…Answered | 1 vote

Hi Matt,

after some investigation I managed to make it woking!
Procedure:

  1. Create [Today] column, date time, default to today, date only
  2. Create calculated column FilterStart = Today+(8/24), result date time -> TODAY 8 AM
  3. Create calculated column FilterEnd = Today+(32/24), result date time -> TOMORROW 8 AM
  4. Delete the column [Today]
  5. Created calculated column IsAfterFilterStart = FilterStart<Modified, result is Yes/No format
  6. Created calculated column IsAfterFilterEnd = FilterEnd>Modified, result is Yes/No format
  7. Update View and set filter to show items only if IsAfterFilterStart = Yes && IsAfterFilterEnd  = Yes

So far nothing new... the problem is the fact filter will not update calculated field each time we select the filtered view.

To resolve that I created simple 3-lines long PowerShell script.

$spWeb = Get-SPWeb -Identity http://server/subsite
$spList = $spWeb.Lists["Display_list_name"]
$spList.Items | ForEach-Object { $_.Update() }

Note: ensure that the server url contains full path to your subsite that contains list you're trying to filter. Also, in second line use the list display name. This three lines should be saved as *.ps1 file.

This script can be executed using SharePoint 2010 Management Shell (C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe). Also, if you schedule it to run every day at 00:01 it will update calculated fields on the regular basis and your view will return filtered entries as expected.

Hope we finaly resolved this issue now.

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Tuesday, January 24, 2012 8:52 PM

I don't think that level of filtering is possible.Simon Rennocks | SharePoint 2010 MCITP & MCPD | Microsoft Community Contributor 2011 | Blog | LinkedIn

Tuesday, January 24, 2012 9:14 PM

Hi Matt,

actually this is possible! Here is what you have to do:

  1. Create a DateTime column and call it [Today]; column type should be DateTime, date only, default to today
  2. Create a calculated column [Filter Start]; Formula: [Today] + (8/24); set output column type to DateTime
  3. Delete [Today] calculated column
  4. Use [Filter Start] in view filter

Note: step 1. is neccessary cause you cannot use volatile function [Today] in the calculated column. However, if you delete it later everything will work as expected.

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Tuesday, January 24, 2012 10:08 PM

Sasa,

Thanks for your help.  I have a couple of questions.  In step 1, did you mean to say use a date/time column instead of a calculated column?  The calculated column doesn't have the option to default to today. 

Also, can you give more detail about how to use the Filter Start column in the view filter?  What value should I put in the filter field? 

Matt H.

Tuesday, January 24, 2012 10:34 PM

Matt,

yes, that was a typo. I chenged the original post.

Step 1 - column type should be DateTime.

About the filter - now I see that my initial proposal will not work because you cannot compare two fields inside the view filter.

Workaround: using the method I defined above you'll be able to set the time span you are interested in (e.g. today 8:00 - tomorrow 8:00). All you neeed to do is to create another calculated column and to set true/false value inside it that will be used by the filter.

So, you have to create two calc columns, [Filter Start] and [Filter End].

Sample formula: =IF (([Filter Start] < [Start Time]) AND ([Filter End] > [Start Time]), "true", "false")

After that use the third calculated field as a filter condition (text compare, true/false value).

PS. Check the formula once again, I wrote it now without testing...

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Wednesday, January 25, 2012 1:28 PM

Thanks for continuing to help me Sasa, but the part that is keeping this from working is the filter, not the column type or content.  The filter is dynamic and the column content is static.  It won't matter what formula we use for the column because it will be executed only when the item is created or modified.  I need the formula to be evaluated each time the page is refreshed and it appears that you can't use formulas in filters down to the hour.  Thanks for trying. Matt H.

Wednesday, January 25, 2012 1:37 PM

That's not true.

Calculated column is dinamicaly calculated each time item is displayed, not at the moment item is created or modified!

Because of that you can use calculated column as described in my previous post, and in your case result of the operation should be filtered view.

As you can see on the attached picture this is possible. I tried on my own server and it works as described in my previous post.

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Wednesday, January 25, 2012 2:04 PM

Just created this sample:

  1. Created [Today] column, date time, default to today, date only
  2. Created calc column FilterStart = Today+(8/24), result date time -> TODAY 8 AM
  3. Created calc column FilterEnd = Today+(32/24), result date time -> TOMORROW 8 AM
  4. Delete the column [Today]
  5. Created IsAfterFilterStart = FilterStart<Modified
  6. Created IsAfterFilterEnd = FilterEnd>Modified

Result:

Now update filter to use IsAfterFilterStart and IsBeforeFilterStart.

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Wednesday, January 25, 2012 2:37 PM

I really appreciate your persistence.  Maybe our SharePoint is broken, but I've found a couple of articles that support what I'm saying is true.  Also, a simple test is to create a calculated column and use the formula =NOW() and select Number as the data type.  After saving, the column will update with something like 40,933.396494537 and you can refresh the view, create another view, etc. and the value won't change.  When you add another item to the list, the new item gets a new value, but the existing ones stay the same.  See image below. 

If you modify the column settings, then all of the values get updated, but not on refresh of the view, which is what I need. 

I think we are just butting up against a limitation in SharePoint.  There's probably some fancy code that would work, but that's beyond my abilities at this point. 

Thank you again for trying to help. 

Matt H.

Wednesday, January 25, 2012 11:29 PM

Matt,

at the end it look like you were right. It looks like calculated columns worked, but it was just because I didn't wait long enough to inspect date changes.

However, will try to find the workaround.

Regards

Sasa Tomicic
MCPD: Shpt Dev 2010, MCITP: Shpt Admin 2010, MCPD: Win Dev 4
My blog: Share(d)Point of View

Friday, January 27, 2012 1:20 PM

Sasa,

Your endurance is amazing.  We've decided to work around it manually for now.  Thanks for all your efforts.  Hopefully there will be an improvement in this area in the next version. 

Matt

Matt H.

Thursday, April 5, 2012 4:44 PM

That solution looks good, i was looking for exactly that, but there's 2 issues for me. The 1st one is that i havew 15k records and updating them all don't looks like a good idea, but I will probably manage it updating only the last records, but the issue that is not allowing it me to get it to work is that power shell 1.0 can't use SharePoint libs, and right now I don't have admin access of my workstation. If you can think about a workarround for that would be great.

Thanks for sharing.