Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Week Ending Column in SharePoint List

Craig Condon

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.

6 Comments. Leave new

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…

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!

Could someone answer a question? If my week ending day is Sunday, do I use 1 in the formula above or *? Thanks!

Could someone answer a question? If my week ending day is Sunday, do I use 1 in the formula above or 8? Thanks!

Craig Condon
May 29, 2015 8:50 am

@Bill: I think you would have to incorporate IF logic into the formula. IF Action Date is Sunday, just use Action Date, but if Action Date is any other day, you would use the formula above, but replace 7 with 8.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\