Group Dates to Weeks Starting Thursday

[Phyllis]

I currently have the left column, but would like to output the right. Week starts on Thursday and the listed "week" date should be the first day of the week.

Date	Week
12/15/2016	12/15/2016
12/16/2016	12/15/2016
12/17/2016	12/15/2016
12/18/2016	12/15/2016
12/19/2016	12/15/2016
12/20/2016	12/15/2016
12/21/2016	12/15/2016
12/22/2016	12/22/2016
12/23/2016	12/22/2016
12/24/2016	12/22/2016
12/25/2016	12/22/2016
12/26/2016	12/22/2016
12/27/2016	12/22/2016
12/28/2016	12/22/2016
12/29/2016	12/29/2016
12/30/2016	12/29/2016
12/31/2016	12/29/2016
1/1/2017	12/29/2016
1/2/2017	12/29/2016
1/3/2017	12/29/2016
1/4/2017	12/29/2016

Thanks!

[Philip · Accepted solution]

This formula will calculate the week start date, assuming [Date] is a date type. The idea is to find the number of days offset from Thursday and subtract them from Thursday's date to get Thursday's date as the week start date.

DateTimeAdd([Date],
Switch(DateTimeFormat([Date], "%a"), 0,
"Fri", -1,
"Sat", -2,
"Sun", -3,
"Mon", -4,
"Tue", -5,
"Wed", -6
)
, 'day')

[gc]

This solution is more generic, I think, where you can specify your 7 day groups of dates based on the assumption that your original column of dates is A) sorted min to max and B) starts on the date you want to begin your 7 day grouping.

[Phyllis]

Thank you! I feel silly not thinking of this!

[moinshaik]

Is there a solution for the above case. I am not able to locate what exactly we need to do. I have a daily data and I need it weekly for example below. Sunday being the weekend day.

10/1/2019	10/6/2019
10/2/2019	10/6/2019
10/3/2019	10/6/2019
10/4/2019	10/6/2019
10/5/2019	10/6/2019
10/6/2019	10/6/2019
10/7/2019	10/13/2019
10/8/2019	10/13/2019
10/9/2019	10/13/2019