Count Weeks in Sequential Year

%3CLINGO-SUB%20id%3D%22lingo-sub-2410108%22%20slang%3D%22en-US%22%3ECount%20Weeks%20in%20Sequential%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2410108%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20fighting%20with%20a%20formula%20for%20way%20too%20long%20and%20I%20hope%20you%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOur%20%E2%80%9Cgas-week%E2%80%9D%20starts%20on%20a%20Friday%20and%20goes%20through%20Thursday.%20I%20want%20to%20assign%20a%20week%20number%20to%20each%20date.%20Seems%20easy%20enough%20with%20WEEKNUM%20(using%2015-Friday%20as%20the%20first%20day%20of%20the%20week)%20or%20ISOWEEKNUM%20(but%20the%20default%20is%20week%20starts%20on%20Monday).%20The%20issue%20arises%20when%20the%20calendar%20flips.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20shows%20a%20formula%20I%20believe%20is%20very%20close%20in%20column%20L%20%26nbsp%3Bbut%20it%20fails%20when%20the%20calendar%20flips%20to%20a%20year%20that%20starts%20on%20Friday%20(i.e.%202021).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20guidance%20or%20help%20you%20can%20provide%20would%20be%20much%20appreciated%20%E2%80%93%20and%20probably%20put%20hair%20back%20in%20my%20head%20an%20years%20on%20my%20life.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2410108%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2410416%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Weeks%20in%20Sequential%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2410416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069774%22%20target%3D%22_blank%22%3E%40kcastrofan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMOD(ISOWEEKNUM(E4-WEEKDAY(E4%2C15))-1%2C52)%2B1%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2411840%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Weeks%20in%20Sequential%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2411840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069774%22%20target%3D%22_blank%22%3E%40kcastrofan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20this%20update%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMOD(ISOWEEKNUM(E4-WEEKDAY(E4%2C15))%2C53)%2B(ISOWEEKNUM(DATE(YEAR(E4)%2C1%2C1))%3D53)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2410554%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Weeks%20in%20Sequential%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2410554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20still%20gets%20confused%20in%20the%20second%20week%20of%202021.%20Expanded%20sample%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22225%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2285%22%3EDay%20of%20Week%3C%2FTD%3E%3CTD%20width%3D%2276%22%3EWeek%20Num%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F1%2F2021%3C%2FTD%3E%3CTD%3EFri%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F2%2F2021%3C%2FTD%3E%3CTD%3ESat%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F3%2F2021%3C%2FTD%3E%3CTD%3ESun%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F4%2F2021%3C%2FTD%3E%3CTD%3EMon%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F5%2F2021%3C%2FTD%3E%3CTD%3ETue%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F6%2F2021%3C%2FTD%3E%3CTD%3EWed%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F7%2F2021%3C%2FTD%3E%3CTD%3EThu%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F8%2F2021%3C%2FTD%3E%3CTD%3EFri%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F9%2F2021%3C%2FTD%3E%3CTD%3ESat%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F10%2F2021%3C%2FTD%3E%3CTD%3ESun%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F11%2F2021%3C%2FTD%3E%3CTD%3EMon%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F12%2F2021%3C%2FTD%3E%3CTD%3ETue%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F13%2F2021%3C%2FTD%3E%3CTD%3EWed%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F14%2F2021%3C%2FTD%3E%3CTD%3EThu%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F15%2F2021%3C%2FTD%3E%3CTD%3EFri%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F16%2F2021%3C%2FTD%3E%3CTD%3ESat%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F17%2F2021%3C%2FTD%3E%3CTD%3ESun%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@kcastrofan 

Perhaps

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

@Sergei Baklan 

 

It still gets confused in the second week of 2021. Expanded sample attached

 

DateDay of WeekWeek Num
1/1/2021Fri1
1/2/2021Sat1
1/3/2021Sun1
1/4/2021Mon1
1/5/2021Tue1
1/6/2021Wed1
1/7/2021Thu1
1/8/2021Fri1
1/9/2021Sat1
1/10/2021Sun1
1/11/2021Mon1
1/12/2021Tue1
1/13/2021Wed1
1/14/2021Thu1
1/15/2021Fri2
1/16/2021Sat2
1/17/2021Sun2

@kcastrofan 

Please check this update

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

@Sergei Baklan 

 

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

@kcastrofan , great, glad it helped

@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/2016Fri52
12/24/2016Sat52
12/25/2016Sun52
12/26/2016Mon52
12/27/2016Tue52
12/28/2016Wed52
12/29/2016Thu52
12/30/2016Fri53
12/31/2016Sat53
1/1/2017Sun52
1/2/2017Mon52
1/3/2017Tue52
1/4/2017Wed52
1/5/2017Thu52
1/6/2017Fri1
1/7/2017Sat1
1/8/2017Sun1
1/9/2017Mon1
1/10/2017Tue1
1/11/2017Wed1
1/12/2017Thu1

@kcastrofan 

Perhaps I overcomplicated it, please check

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

in attached.