Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Fiscal Year Week Number
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.
Best Answers
-
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, whenWEEKNUMBER(some_date_in_January_or_March)-13isless 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 )
-
This is very helpful. Thank you!
Answers
-
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, whenWEEKNUMBER(some_date_in_January_or_March)-13isless 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 )
-
This is very helpful. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.2K Get Help
- 451 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- =IFERROR(AVG(COLLECT({Score BA}, {Employee Email BA}, $Variable@row, {Date of Review BA}, ISDATE(@cell), {Date of Review BA}, YEAR(@cell) = $[Last Year]$102, {Date of Review BA}, MONTH(@cell) = [Last …
- Hi, I'm trying to write a formula that will look at a particular column (in this case the "extrusion" column) and return the first entry that has a check in the "cycle count is due column". My current…
- I am trying to get a count for a date range, but am getting a number that is one less compared to the filter. Current Tuesday Formula: =IFERROR(TODAY() + (3 - WEEKDAY(TODAY())) - IF(OR(WEEKDAY(TODAY()…