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. DateWeek12/15/201612/15/201612/16/201612/15/201612/17/201612/15/201612/18/201612/15/201612/19/201612/15/201612/20/201612/15/201612/21/201612/15/201612/22/201612/22/201612/23/201612/22/201612/24/201612/22/201612/25/201612/22/201612/26/201612/22/201612/27/201612/22/201612/28/201612/22/201612/29/201612/29/201612/30/201612/29/201612/31/201612/29/20161/1/201712/29/20161/2/201712/29/20161/3/201712/29/20161/4/201712/29/2016 Thanks!

Group Dates to Weeks Starting Thursday

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!

Date Time

Accepted answers

Philip

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')

All comments

Philip

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

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 starts on the date you want to begin your 7 day grouping.

weekdategroup.yxmd

Phyllis

Thank you! I feel silly not thinking of this!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703