Filter List View by Current Week

If you want to be able to filter the list to only show items where “Date” is this week,

Most common option that appears first is to filter for the last seven days, using a calculated column for [Date]+7 with formula like

=DATE(YEAR([Created]),MONTH([Created]),DAY([Created])+7)

and filtering the view where this new calculated column is >=[Today]

But it is really last 7 days not the week period. I.e. If today is Wednesday, view should display items from Monday to upcoming Sunday (actual current week)

For this solution replace the <<Date>> with appropriate DateTime field base on which you want to filter the view.

  1. Create a calculated column named WeekStart returning type DateTime, with the calculation:

    =IF(TEXT(WEEKDAY(<<Date>>),”ddd”)=”Mon”,<<Date>>,IF(TEXT(WEEKDAY(<<Date>>-1),”ddd”)=”Mon”,<<Date>>-,IF(TEXT(WEEKDAY(<<Date>>-2),”ddd”)=”Mon”,<<Date>>-2,IF(TEXT(WEEKDAY(<<Date>>-3),”ddd”)=”Mon”,<<Date>>-3,IF(TEXT(WEEKDAY(<<Date>>-4),”ddd”)=”Mon”,<<Date>>-4,IF(TEXT(WEEKDAY(<<Date>>-5),”ddd”)=”Mon”,<<Date>>-,<<Date>>-6))))))

  2. Create a calculated column named WeekEnd returning type <<Date>>time, with the calculation: =IF(TEXT(WEEKDAY(<<Date>>),”ddd”)=”Sun”,<<Date>>,IF(TEXT(WEEKDAY(<<Date>>+1),”ddd”)=”Sun”,<<Date>>+1,IF(TEXT(WEEKDAY(<<Date>>+2),”ddd”)=”Sun”,<<Date>>+2,IF(TEXT(WEEKDAY(<<Date>>+3),”ddd”)=”Sun”,<<Date>>+3,IF(TEXT(WEEKDAY(<<Date>>+4),”ddd”)=”Sun”,<<Date>>+4,IF(TEXT(WEEKDAY(<<Date>>+5),”ddd”)=”Sun”,<<Date>>+5,<<Date>>+6))))))
  3. Create a view with the following filter:

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

Advertisements

36 Comments

  1. May 14, 2008 at 2:24 PM

    […] Filter List View by Current Week […]

  2. Daniel said,

    May 28, 2008 at 12:21 PM

    Hi

    Do you have any idéa of geeting a filter for current month, and one for last month, lets say, added items?

  3. June 19, 2008 at 8:42 AM

    How would you do this to filter by current month?

  4. July 9, 2008 at 8:32 AM

    The first demo worked just fine, but the actual week demo fails with “The formula contains a syntax error or is not supported.”

    I replaced the <> with my field [DueDate] but that did not work.

    Also copy paste gives some funny chars regarding the ” chars surrounding the ddd.

    Any hints

  5. Sigitas said,

    July 16, 2008 at 12:00 PM

    Nice tip, however quite long formula and I think you will have some problems in the non English Sharepoint sites. My suggestion would to use next formulas for steps:

    1. =[Date]-WEEKDAY([Date],3)

    2. =[Date]-WEEKDAY([Date],3)+6

    Of course, replace [Date] with your date column name 😉

  6. Hakan Yuksel said,

    July 16, 2008 at 12:43 PM

    Sorry, but the computed columns in this example are so wrong at so many levels, that I just have to say something.

    (a) WEEDAY() is misused
    (b) TEXT() is misused and unnecessary
    (c) IF()s are unnecessary

    To use TEXT(WEEKDAY(),”ddd”) is very strange. I’ve seen this used in Microsoft documents, but quite frankly it’s wrong! All that is needed is simply TEXT(,”ddd”), without using WEEKDAY().

    Why does WEEKDAY() “seem” to work? It’s because WEEKDAY() returns a number between 1 and 7, but you tell TEXT() to treat its first argument as a date by using the “ddd” format, so the numbers between 1 and 7 works out to be the dates from 01-Jan-1900 to 07-Jan-1900, which just happens to be the days from Sunday to Saturday. I guess 2 wrongs here do make a right!

    BUT BEWARE! WEEKDAY() can actually take an optional second argument (it can start the week on Monday, and also return 0 to 6 instead of 1 to 7). And when you use the second argument to return the non-default values, you’ll end up with some very strange results.

    BUT! If WEEKDAY() returns numbers, why convert it to text??? Just compare them to numbers 1 to 7!

    BUT WAIT! If WEEKDAY() returns a number, why not use math on the date? There’s no need for so many nested ifs, in fact, there is no need for an IF() or the TEXT() at all!

    Here’s a better solution, using WEEKDAY() properly:

    If your week starts on Sunday:
    WeekStart
    =[mydate] – (WEEKDAY([mydate]) – 1)
    WeekEnd
    =[mydate] + (7 – WEEKDAY([mydate]))

    If your week starts on Monday:
    WeekStart
    =[mydate] – (WEEKDAY([mydate],2) – 1)
    WeekEnd
    =[mydate] + (7 – WEEKDAY([mydate],2))

    • Lian said,

      November 5, 2009 at 1:31 AM

      This helps alot. Thanks for the update!!

    • Iain Munro said,

      February 7, 2010 at 5:05 AM

      Hi There

      Much simpler, however, I get Formula not supported for this.

      Any ideas?

      Iain

  7. AbstractSpace said,

    July 16, 2008 at 12:54 PM

    Actually this formula was created ages ago for old wss site,There was another Computed filed based on this. So I just moved this one to MOSS 2007 list without much changes.

    But you are right.
    Thanks for this Comment
    I will update the post as per

  8. Paul Shaver said,

    September 26, 2008 at 1:37 PM

    Hakan Yuksel, that was exactly what I needed. Thank you!

  9. ericstepek said,

    November 18, 2008 at 5:33 PM

    Hakan Yuksel, thank you for the tip. It was what I needed as well! Thank you very much. One additional question. On a reoccuring calendar item, the End Time column is some time in 2066. How could you change the fomula to just display reoccuring events instances that are in the current week?

  10. Michael said,

    February 19, 2009 at 2:50 PM

    hi,
    how would I filter only items that have a date from any day from last week (Monday-Sunday) ?

  11. masha said,

    March 17, 2009 at 9:49 AM

    filter by current month:
    [today]>=01.month([date]).year([date])
    &&
    [today]<=31.month([date]).year([date])

  12. ken said,

    March 23, 2009 at 2:18 PM

    But the calculated value is only calculated at the time the task is initially created, right?

    Is it possible to create a filter that says, any item *modified* the past week (for instance)?

  13. Mark Curtin said,

    March 27, 2009 at 2:27 PM

    What if I want a 2 week look ahead (ie this week and next). I tried this.

    2 New Columns
    WeekStart
    =[Start Time]-(WEEKDAY([Start Time],2)-1)

    NextWeekEnd
    =[Start Time]-WEEKDAY([Start Time],3)+13

    and then a view that

    Column: WeekStart = [Today]

    But its not working. Any thoughts?

  14. Dani said,

    March 27, 2009 at 3:13 PM

    The formula for calculating what has been done the past week is great. How do I add a formula to show me what has not been updated in the past month?

  15. Tordis Wik said,

    March 30, 2009 at 1:26 PM

    If you want the last week you can add +7 to Hakan Yuksel formula.

    WeekStart
    =[mydate] – (WEEKDAY([mydate],2) – 1)+7
    WeekEnd
    =[mydate] + (7 – WEEKDAY([mydate],2))+7

    Column: WeekStart = [Today]

    and you do the same if you want the past 2dweeks. shange WeekEnd to:
    =[mydate] + (7 – WEEKDAY([mydate],2))+14

  16. Summer said,

    April 28, 2009 at 11:37 PM

    How would I go about filtering by year?

  17. September 4, 2009 at 4:14 PM

    Hello, you can use =TEXT(YEAR(StartTime),”000″)

    http://www.sharepoindesigners.net

  18. Ryan said,

    November 27, 2009 at 4:29 PM

    I’ve used a similar technique to filter items for the current month
    http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

  19. Krishnan said,

    February 17, 2010 at 4:45 AM

    Great post! The main post and comments helped a lot to achieve the output I desired. Thanks a bunch!

  20. James Ewing said,

    April 8, 2010 at 1:43 AM

    Thanks for the post!!!

    Note: I had trouble getting the formulas to work properly (WSS).

    The final formulas that worked for me are as follows:
    Start:
    =[Planned Start]-(WEEKDAY([Planned Start])-1)

    End:
    =[Planned Finish]+(7-WEEKDAY([Planned Finish]))

  21. Jason Santo said,

    September 3, 2010 at 1:37 AM

    This post and the comments herein really helped me out with some work I was doing. Thank you so much!

  22. Chris G. said,

    May 18, 2012 at 1:44 AM

    I was able to create a view where it groups the items in the list by week.
    In this example I’m using the “Created” date stamp.

    Columns.(create 3 calculation columns)
    Startweek: (Date format)
    =[Created]-WEEKDAY([Created])+1
    EndWeek: (Date format)
    =[Created]+7-WEEKDAY([Created])
    Week: (single line of text format)
    =TEXT(StartWeek,”mmm-dd-yyyy”)&TEXT(EndWeek,” mmm-dd-yyyy”)

    View: Group by “Week”

  23. Michael Rose said,

    August 17, 2012 at 3:05 AM

    Wow what a mess! Everyone is trying to make this so much more difficult than it is. If you want to have a weekly view then just use the =WEEKDAY(Created) function and create a calculated column of type number. Each entry will have a number value 1 – 7. 1 being Sunday and 7 being Saturday. Just make a filter that is greater than 1 and less than seven and there you have it – so simple.

    • Ryan said,

      August 17, 2012 at 12:57 PM

      @Michael – whilst I agree with you about the over complex thing your solution will not work at all!

      Think about it – you’re turning every date into an int of 1(Sun) to 7 (Sat) – then filtering for things between 1 and 7 – thats just going to show EVERYTHING from all weeks?

      Try it.

  24. davish said,

    August 29, 2012 at 2:19 AM

    Does anyone know how to filter Wednesday – Wednesday? So instead of filtering Mon-Sunday (the above formula), how can I go about using the formula above? Right now I have 2 columns:

    =[Due Date]-(WEEKDAY([Due Date],2)-1)

    =[Due Date]+(7-WEEKDAY([Due Date],2))

    They display the data mon-sun, but I want it Wed-Wed.. any help would be appreciated, thank you!

  25. Lyle said,

    January 28, 2013 at 8:09 PM

    How would one filter from last week Friday to Thursday the current week when the list is viewed Friday the current week. The current weeks Friday tasks must not included in the view. Thanks

  26. Marcelo said,

    February 10, 2013 at 9:41 AM

    Thanks for every other excellent article. The place else may anybody
    get that kind of info in such an ideal approach of writing?
    I have a presentation next week, and I’m at the search for such info.

  27. Heidi said,

    August 7, 2013 at 9:33 AM

    Hello there! This is kind of off topic but I need some
    guidance from an established blog. Is it hard to set up your own
    blog? I’m not very techincal but I can figure things out pretty fast. I’m
    thinking about making my own but I’m not sure where to begin. Do you have any tips or suggestions? Appreciate it

  28. February 12, 2016 at 9:29 PM

    There are a couple of small typos in the function -1 and -5 are missing

    =IF(TEXT(WEEKDAY([Date and Time]),”ddd”)=”Mon”,[Date and Time],IF(TEXT(WEEKDAY([Date and Time]-1),”ddd”)=”Mon”,[Date and Time]-1,IF(TEXT(WEEKDAY([Date and Time]-2),”ddd”)=”Mon”,[Date and Time]-2,IF(TEXT(WEEKDAY([Date and Time]-3),”ddd”)=”Mon”,[Date and Time]-3,IF(TEXT(WEEKDAY([Date and Time]-4),”ddd”)=”Mon”,[Date and Time]-4,IF(TEXT(WEEKDAY([Date and Time]-5),”ddd”)=”Mon”,[Date and Time]-5,[Date and Time]-6))))))

  29. Chris Mitchell said,

    March 9, 2016 at 9:34 PM

    What is [mydate]? I have tried today’s date, I have tried the date of a Monday which is the beginning of the week I am trying to achieve…… I still have not reached where the start of the week lands on Monday every time in a calculated column. Please help.

  30. Cindy said,

    January 20, 2017 at 2:59 AM

    Thank you!

    I’ve been trying to figure this out for two days and just like that (finger snap) you have exactly what I need. Kudos to you!!


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: