Home > development, MOSS, MS SharePoint Sever, sharepoint, sps, Uncategorized > Filter Views based on a Week

Filter Views based on a Week

Following post will help you filter views for Current Week items, Previous All Week(s) items and Next All Week(s) items.

Create a “WeekStart” calculated column.

Use the following formula and replace “Week Commencing” with the date you want to use in the formula e.g. Created etc.

Also, select “Date and Time” as a datatype returned by the formula.


=IF(TEXT(WEEKDAY([Week Commencing]),"ddd")="Mon",[Week Commencing],IF(TEXT(WEEKDAY([Week
Commencing]-1),"ddd")="Mon",[Week Commencing]-1,IF(TEXT(WEEKDAY([Week Commencing]-2),"ddd")="Mon",[Week

Commencing]-2,IF(TEXT(WEEKDAY([Week Commencing]-3),"ddd")="Mon",[Week

Commencing]-3,IF(TEXT(WEEKDAY([Week Commencing]-4),"ddd")="Mon",[Week

Commencing]-4,IF(TEXT(WEEKDAY([Week Commencing]-5),"ddd")="Mon",[Week Commencing]-5,[Week

Commencing]-6))))))

Create a “WeekEnd” calculated column.

Use the following formula and replace “Week Commencing” with the date you want to use in the formula e.g. Created etc.

Also, select “Date and Time” as a datatype returned by the formula.


=IF(TEXT(WEEKDAY([Week Commencing]),"ddd")="Sun",[Week Commencing],IF(TEXT(WEEKDAY([Week
Commencing]+1),"ddd")="Sun",[Week Commencing]+1,IF(TEXT(WEEKDAY([Week Commencing]+2),"ddd")="Sun",[Week
Commencing]+2,IF(TEXT(WEEKDAY([Week Commencing]+3),"ddd")="Sun",[Week

Commencing]+3,IF(TEXT(WEEKDAY([Week Commencing]+4),"ddd")="Sun",[Week

Commencing]+4,IF(TEXT(WEEKDAY([Week Commencing]+5),"ddd")="Sun",[Week Commencing]+5,[Week

Commencing]+6))))))

Now Create Views:

Items from Current Week only:

WeekStart<=[Today] AND WeekEnd>=[Today]

Items from Next Week(s) only:

WeekStart>=[Today]

Items from Previous Week(s) only:

WeekStart>=[Today] OR WeekEnd<=[Today] AND WeekStart<=[Today]

Hope this helps–
Rehman.

Advertisements
  1. October 6, 2010 at 1:32 pm

    You can simply that formula a fair bit – e.g.

    Start Week =[Due Date] – WEEKDAY([Due Date]) +1
    End Week =[Due Date] +7 – WEEKDAY([Due Date])

    More info – http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

  2. October 7, 2010 at 12:02 am

    Yep, you are right…..however, the formula above is perhaps a bit more readable…….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: