A common requirement in SharePoint is to filter lists so you’re only showing the current week, month, quarter etc. If you only want to see the last X number of days (e.g. last 30 days) then it’s simple enough.
If you want setup a SharePoint view to see the current calendar week then its a little more complex – especially if you define your week differently from the standard Sunday to Saturday or Monday to Sunday.
First you must look at and really understand the method for setting up SharePoint views to filter the current calendar month or week as it’s not really obvious.
Long story short – here are the steps :-
- Use one of the calculated column formula below to find the “Start of week“
- Use a calculated column formula of “End of Week“
= [Start of Week] + 6
- Setup a View Filter showing items when [Today] is in that range.
Start of Week <= [Today] AND End Of Week >= [Today]
Again – see this post for showing the method in step-by-step detail.
If your week goes Sunday to Saturday or Monday to Sunday then use :-
| Start of week | End of week | |
| Sunday to Saturday |
=[Due Date] - WEEKDAY([Due Date]) +1 |
=[Start of Week] + 6 |
| Monday to Sunday |
=[Due Date] – WEEKDAY([mydate],2) + 1 |
=[Start of Week] + 6 |
Obviously you will replace [Due Date] with your own particular date field.(Check out the WEEKDAY function to understand what the 2nd argument does)
The other options are a bit more complex to find the start of the week (again end of week = start + 6)
| Start of week | |
| Tuesday to Monday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<3,-4,3) |
| Wednesday to Tuesday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<4,-3,4) |
| Thursday to Wednesday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<5,-2,5) |
| Friday to Thursday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<6,-1,6) |
| Saturday to Friday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<7,0,7) |
See this picture for some test data, its in a strange order (Thursday to Wednesday if first) but check it out on your desk calendar if you’re not convinced!
Tags: Calculated Columns, Guide, Tip
Really amazing for your solution.
I need a solution for showing the events in last week, this week and next week separately but can’t use the [Today]+7 solution in a simple way. really thanks for your solution.
Thank you so much!!! This works beautifully on SharePoint 2013.
I also needed to get a view that shows last week’s items, so I modified the view slightly:
Where
Start of Week = [Today]-7
Solved my problem, thank you so much. Already had a Wk Ending column but didn’t think about a Wk Starting.
THANK YOU! Elegant and easy, like all the best solutions are.
use semi instead of column if you get error for if
=[FoodDateSearch]-(WEEKDAY([FoodDateSearch])+IF(WEEKDAY([FoodDateSearch])<7;0;7))
https://egilhansen.com/2013/02/14/sharepoints-formula-syntax-can-change-depending-on-a-sites-regional-setting/
I want to use a filter for the current time how can I use it ?
this is amazing! I still don’t know how to create one for last week only (mon through sun). I created a “start of next week” and put in formula =[Message Date]-WEEKDAY([Message Date],2)+8, but it still gives me the following sunday as part of next week (should be excluded) so next week’s view has a sun through sat instead of mon through sun. i put the filter as [today]+7. any advice?