Count Weeks in Sequential Year

[kcastrofan]

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!

[#2]

@kcastrofan 

Perhaps

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

[#3]

@Sergei Baklan 

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

[#4]

@kcastrofan 

Please check this update

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

[#5]

@Sergei Baklan 

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

[#6]

@kcastrofan , great, glad it helped

[#7]

@Sergei Baklan 

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.

Thank you in advance for the follow up.

12/23/2016	Fri	52
12/24/2016	Sat	52
12/25/2016	Sun	52
12/26/2016	Mon	52
12/27/2016	Tue	52
12/28/2016	Wed	52
12/29/2016	Thu	52
12/30/2016	Fri	53
12/31/2016	Sat	53
1/1/2017	Sun	52
1/2/2017	Mon	52
1/3/2017	Tue	52
1/4/2017	Wed	52
1/5/2017	Thu	52
1/6/2017	Fri	1
1/7/2017	Sat	1
1/8/2017	Sun	1
1/9/2017	Mon	1
1/10/2017	Tue	1
1/11/2017	Wed	1
1/12/2017	Thu	1

[#8]

@kcastrofan 

Perhaps I overcomplicated it, please check

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

in attached.