How to auto-populate my company's fiscal year week number
Hello,
I am having a formula in the "number of chambers shipped" to add them from a different sheet.
the "date" column will auto-populate
I would like for the Week column to auto-populate my company's fiscal year. I have no idea how to do that. Can someone help me figure this out?
Best Answer
-
Hi Diego,
If your fiscal year starts on January 1st you can use =weeknumber(date@row)
If your fiscal year is not January 1st you'll need a different formula. If your fiscal years starts on October 1st the formula would be:
=ROUND(((Date@row - DATE(2021, 10, 1)) / 7) + 0.49, 0)
Substitute the 1st day of your fiscal year for DATE(2021, 10, 1)
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Diego,
If your fiscal year starts on January 1st you can use =weeknumber(date@row)
If your fiscal year is not January 1st you'll need a different formula. If your fiscal years starts on October 1st the formula would be:
=ROUND(((Date@row - DATE(2021, 10, 1)) / 7) + 0.49, 0)
Substitute the 1st day of your fiscal year for DATE(2021, 10, 1)
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you so much! this worked like a charm and I really appreciate how you explained in detail since I have no idea how to work with formulas! thank you once again!
-
Hi Diego,
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk - this formula works perfect, curious can this formula be continued into the next fiscal year where it will update and start over at week 1?
Adriane
-
@Mark Cronk How would be able to modify the formula if there are 52 FY weeks instead of 56?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives