I was recently working on an excel pivot table report that pulls its data from a SharePoint list. One of the business requirements was to be able to sort, filter, and/or group by a “Week Ending” date. The list has a column called Action Date which could be any day/date. The solution turned out to be quite simple.
Create a new column in the list called, for example, “Week Ending.” Make this column a calculated column with the following formula:
=TEXT([Action Date]+(7-WEEKDAY([Action Date])),”mm/dd/yyyy”)
Replace [Action Date] with the column off which you are basing the week ending date.
This particular formula assumes that Saturday (7) is the week ending date. Adjust accordingly.