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.
3 Comments
Very helpful, thanks. We’re in “we need that Dashboard NOW*!” mode.
*translation, we could’ve done it 6 months ago, Boss, if you hadn’t pushed 5 other priority projects to the top of the list…
Good words.
Thanks very much – this was indeed helpful. When I was exported the previous text field (list of dates) to Excel, my excel calculations were not working properly. This definitely fixes it and ensure that time entered during the week is reflected as a weekly Friday time entry.
Thanks!