Forum Discussion
Count Weeks in Sequential Year
I have been fighting with a formula for way too long and I hope you can help.
Our “gas-week” starts on a Friday and goes through Thursday. I want to assign a week number to each date. Seems easy enough with WEEKNUM (using 15-Friday as the first day of the week) or ISOWEEKNUM (but the default is week starts on Monday). The issue arises when the calendar flips.
The attached file shows a formula I believe is very close in column L but it fails when the calendar flips to a year that starts on Friday (i.e. 2021).
Any guidance or help you can provide would be much appreciated – and probably put hair back in my head an years on my life. Thanks!
7 Replies
It still gets confused in the second week of 2021. Expanded sample attached
Date Day of Week Week Num 1/1/2021 Fri 1 1/2/2021 Sat 1 1/3/2021 Sun 1 1/4/2021 Mon 1 1/5/2021 Tue 1 1/6/2021 Wed 1 1/7/2021 Thu 1 1/8/2021 Fri 1 1/9/2021 Sat 1 1/10/2021 Sun 1 1/11/2021 Mon 1 1/12/2021 Tue 1 1/13/2021 Wed 1 1/14/2021 Thu 1 1/15/2021 Fri 2 1/16/2021 Sat 2 1/17/2021 Sun 2 Please check this update
=MOD(ISOWEEKNUM(E4-WEEKDAY(E4,15)),53)+(ISOWEEKNUM(DATE(YEAR(E4),1,1))=53)
