Fiscal Year Week Number

RayF

Hi Everyone, I know this topic has been covered but I can't seem to figure it out. I am trying to calculate the week number if my FY starts on April 1, 2022. CY year is no issue, I used =weeknumber(today(-1). Thanks for your help.

Toufong Vang

Your organization's FY starts 13 weeks after the start of the CY. So, for your organization, the fiscal week is CY_Week - 13 , or... WEEKNUMBER([Test Date]@row) - 13 .

However, that becomes problematic for January through the end of March when... WEEKNUMBER(some_date_in_January_or_March)-13 ...results in a negative value or 0. You'll need to add 52 to resolve this. That is to say, when WEEKNUMBER(some_date_in_January_or_March)-13 is less than or equal to 0, add 52 ...

IF( (WEEKNUMBER([Test Date]@row)-13) <= 0 , WEEKNUMBER([Test Date]@row) - 13 + 52 , WEEKNUMBER([Test Date]@row) - 13 ) which simplifies to...

IF( (WEEKNUMBER([Test Date]@row)-13) <= 0 , WEEKNUMBER([Test Date]@row)+39 , WEEKNUMBER([Test Date]@row)-13 )

If "Test Date" is Today(), then your formula will be...

IF( (WEEKNUMBER( Today() )-13) <= 0 , WEEKNUMBER( Today() )+39 , WEEKNUMBER( Today() )-13 )

RayF

This is very helpful. Thank you!

Toufong Vang

Your organization's FY starts 13 weeks after the start of the CY. So, for your organization, the fiscal week is CY_Week - 13 , or... WEEKNUMBER([Test Date]@row) - 13 .

However, that becomes problematic for January through the end of March when... WEEKNUMBER(some_date_in_January_or_March)-13 ...results in a negative value or 0. You'll need to add 52 to resolve this. That is to say, when WEEKNUMBER(some_date_in_January_or_March)-13 is less than or equal to 0, add 52 ...

IF( (WEEKNUMBER([Test Date]@row)-13) <= 0 , WEEKNUMBER([Test Date]@row) - 13 + 52 , WEEKNUMBER([Test Date]@row) - 13 ) which simplifies to...

IF( (WEEKNUMBER([Test Date]@row)-13) <= 0 , WEEKNUMBER([Test Date]@row)+39 , WEEKNUMBER([Test Date]@row)-13 )

If "Test Date" is Today(), then your formula will be...

IF( (WEEKNUMBER( Today() )-13) <= 0 , WEEKNUMBER( Today() )+39 , WEEKNUMBER( Today() )-13 )

RayF

This is very helpful. Thank you!