How can I filter a list based on the day of the week?

Question

We have a Sharepoint list which contains a user's name, various areas of duty, and the weekday name for that roster. For example:

Name Recess Lunch  Day
Joe  Area 1 Area 3 Monday
Fred Area 4 Area 2 Tuesday

Is there a way to filter this list based on the Day column? We've tried modifying the view to select items where Day = TEXT(Today,"dddd"), however that did not return any records.

The data is not likely to change much, but we do need to be able to modify an individual record in special cases such as relief staff members etc.

Answer 1

Answering my own question, as we figured out a workaround. It's a bit of a kludge, but it works.

1. Create the list as normal. Use a text or choice column to store the day. I'm not sure if it works with a lookup table.
2. Add a text column called (for example) CurrentDay. This column will be updated by a script.

3. Create a third column called "Days Match". This is a calculated column with the formula

   =IF(Day=CurrentDay,"yes","no")

4. Create a new view called "Day View" with a filter where "Days Match" is equal to "yes".

5. Store the following script on your sharepoint server, which updates the CurrentDay column with the weekday name:

$spWeb = Get-SPWeb -Identity http://sharepointserver

$spList = $spWeb.GetList("/Lists/YourList") foreach ($spListItem in $spList.items) {

$CurrentDay = Get-Date -UFORMAT %A

$spListItem["CurrentDay"] = $CurrentDay $spListItem.Update()

}

1. Create a scheduled task to run the above script every day.

As I said, it's a kludge, but it works. I hope it helps someone else.

Answer 2

Another approach that you could use is to filter the list by using javascript and a content editor web part.

Add a content editor web part to your list page and use the html source editor to add the following javascript (this is just a rough example)

<script>

function FilterByDay(){
    var d=new Date();
    var weekday=new Array(7);
    weekday[0]="Sunday";
    weekday[1]="Monday";
    weekday[2]="Tuesday";
    weekday[3]="Wednesday";
    weekday[4]="Thursday";
    weekday[5]="Friday";
    weekday[6]="Saturday";

    var url = document.location.href;
    if(url.indexOf("FilterField1")==-1){
       var joiner = "?";
       if(url.indexOf("?")!=-1){
          joiner = "&";
       }
       document.location.href = url + joiner + "FilterField1=Day&FilterValue1=" + weekday[d.getDay()];
    }
}

FilterByDay();

</script>