Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, November 29, 2012 1:10 PM | 1 vote
Hi!
I have a list that contains three fields, Date (Date and Time), Title (Single line of text) and Description (Single line of text). What I want to do is to filter this list to show only the items with dates in the current week aswell as the coming week. I know I have to create some calculated columns but I'm stumped as to what the formulas should be. I tried Googling it but I can't get it to work. Anyone done anything similar before?
All replies (15)
Thursday, November 29, 2012 5:25 PM ✅Answered | 3 votes
I do this on a timesheet list - I added a calculated field called WeekEnd output as a date/time
=[DateField]+(7-WEEKDAY[DateField])) - that gave me a Sunday date.
Then I could create a view based on that field
This week would be
[WeekEnd] is greater than or equal to [Today]+1 AND
[WeekEnd] is less than or equal to [Today]+7
The week ending date has to be greater than today, but not more that 7 days ahead. If you wanted next week as well, just change the +7 to +14.
Robin
Friday, November 30, 2012 9:38 PM ✅Answered | 3 votes
My fault, I forgot a paren.
=[DateField]+(7-WEEKDAY**(**[DateField]))
Oh, and this gives a Saturday as the Weekend date. If you want Sunday, make it 8-
Robin
Thursday, November 29, 2012 1:24 PM
Maybe this post can help you:
https://www.nothingbutsharepoint.com/sites/eusp/Pages/filter-by-events-this-week-in-a-sharepoint-calendar.aspx
I know it's for 2007, but I suppose it's the same for 2010.
Thursday, November 29, 2012 1:47 PM
Hi Kewa,
Is Date field get updated with current date? If yes you can modify the list view to have the filter on date column as Date >=[Today] AND Date<=[Today]+13, provided date field should get updated with the current date.
Regards,
Ajeet
If my post is helpful - please click on the green arrow to mark it as answer
Thursday, November 29, 2012 2:41 PM
Hey!
Thx for the link and the suggestion, I've tried that before but it doesn't show the earlier items in the week since it sorts by [Today] . What I need is some way to define a week and show all items for that specific week and the week after that.
Thursday, November 29, 2012 3:13 PM
Hi Kewah,
Have you tried removing the sort in a view and applying filter in the preceding post?
Thanks,
Ajeet
If my post is helpful - please click on the green arrow to mark it as answer
Thursday, November 29, 2012 3:32 PM
Yes I've tried that and I still only get the items for today and 14 days forward, what I need to see are the items from this Monday to next Sunday.
Friday, November 30, 2012 8:11 AM
Robin: Thx for the suggestion, and I think it's something like that that I need but I keep on getting an error message whenever I try the formula, the standard "The formula contains a syntax error or is not supported". My Date column is set up as Date and Time, Date and Time Format: Date Only, Default Value: (None). Any ideas how to fix it?
Monday, December 3, 2012 8:49 AM
Big thx, eventhough I tried adding the missing parentheses it didn't work, however when I tried it this morning again it did so problem solved!
Friday, December 13, 2013 12:34 PM
This is a very useful comment, thanks.
Gets round the fact that [Today] is unavailable in calculated columns, but possible in filters.
Friday, December 13, 2013 12:37 PM
A better way may be use the syntax =[DateField]+(7-WEEKDAY**(**[DateField]),2)
This means Weekday([DateField],2) will return 1 for a Monday, 2 for a Tuesday etc. and 7 for Sunday
See Microsoft webpage on WEEKDAY function for details.
Thursday, June 12, 2014 3:03 PM
It worked for me on SharePoint 365 (12/06/2014):
Then you can Filter on [Completed Date] as you would a normal date.
Only different i did was set the complete date as a Date and time (Date only)
and Formula is: =IF([Task Status]="Completed",TEXT(Today,"DD/MM/YYYY"),"")
Thanks,
Martin
Friday, October 10, 2014 1:31 PM
Thanks
Tuesday, March 29, 2016 7:27 AM
Hi,
If you would like to see items in a list that were created one (1) week before and after today based on a date field then I found that this works. Create a view (i.e. Past and Coming Week Items) and create two filters in the "Filter" section.
Filter #1 =
"Show the items when column" (select from drop-down) [your date field]
(select from drop-down) "is greater than or equal to"
and enter this code [Today]-7
Select radial button "Or"
Filter #2=
"Show the items when column" (select from drop-down) [your date field]
(select from drop-down) "is less than or equal to"
and enter this code [Today]+7
This will list all items that correspond to the prior week and the coming week after today's date based on the selected field of dates. I hope this helps.
Wednesday, January 30, 2019 7:32 AM
excellent, it helped.