# Fiscal Year Week Number

Options
✭✭✭

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.

• ✭✭✭✭✭
Options

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 )

• ✭✭✭
Options

This is very helpful. Thank you!

• ✭✭✭✭✭
Options

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 )

• ✭✭✭