New Contributor

# 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

# Re: Count Weeks in Sequential Year

Perhaps

``=MOD(ISOWEEKNUM(E4-WEEKDAY(E4,15))-1,52)+1``

# Re: Count Weeks in Sequential Year

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

# Re: Count Weeks in Sequential Year

``=MOD(ISOWEEKNUM(E4-WEEKDAY(E4,15)),53)+(ISOWEEKNUM(DATE(YEAR(E4),1,1))=53)``

# Re: Count Weeks in Sequential Year

Tip of the cap to you sir! That worked perfectly.

# Re: Count Weeks in Sequential Year

@kcastrofan , great, glad it helped

# Re: Count Weeks in Sequential Year

I apologize for the delayed follow up. But upon further inspection the formula mishandles years where week 53 starts on a Friday - the following partial week is assigned a week 52. It should be a continuation of week 53. Sample file is attached.

``=WEEKNUM(E4-WEEKDAY(E4,15)+1)``