Fiscal Week (in a Quarter) based on a Date Formula
Hi,
I am struggling putting together a forumla that would calculate Fiscal Week (13 FW in a FQ) based on a date and taking in consideration that our FY starts February 1. Any help/advice would be appreciated!
Nataša
Answers
-
Try something like this...
=INT(([Date Column Name]@row - DATE(YEAR([Date Column Name]@row), 02, 01)) / 13)
-
Hi Paul,
The formula works, but I am getting undesired results, i.e. week 17 for August 20, which is week 4 of Q3. Any idea? Thank you.
-
If the FY start is 1 Feb then 20 Aug is 29 weeks into the FY. That means week 3 of Q3. This formula should output that for you...
=MOD(INT((Date@row - DATE(YEAR(Date@row), 2, 1)) / 7) + 1, 13)
-
Hi Paul,
Thank you for your help. I tried the new formula, but it also does not show correct FW.
Our fiscal weeks start on Saturday, so I guess that is why for us Aug 20 is the first day of W4 as for you is W3. And As you can see there are results with W0. Can these details be fixed? Thank you.
-
Yes. Feb 1 is a Tuesday, so I assumed you were starting on Feb 1. In your case, the first day of the first fiscal week is Jan 29 correct?
-
Hi Paul, yes, that is correct; the first day of Q1 22 was Jan 29. When I added this date to the formula I got the right numbers. Thank you for baring with me and your help. Much appreciated!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives