Formula to show date in Fiscal Year
I located the formula to create a Fiscal Year from a date (January to December)
="Q" + ROUNDUP((MONTH(Date@row) / 4)) + " " + YEAR(Date@row)
1/7/2021 = Q1 2021
But our company Fiscal Year is November 1 to October 31. I cannot figure out how to edit this formula to work.
Thanks!
Best Answer
-
Presuming you have column named Date with a date in it:
=IF(MONTH(Date@row) > 10, "Q1 " + (YEAR(Date@row) + 1), IF(MONTH(Date@row) = 1, "Q1 " + YEAR(Date@row), IF(MONTH(Date@row) > 7, "Q4 " + YEAR(Date@row), IF(MONTH(Date@row) > 4, "Q3 " + YEAR(Date@row), "Q2 " + YEAR(Date@row)))))
Hope this helps!
Answers
-
Presuming you have column named Date with a date in it:
=IF(MONTH(Date@row) > 10, "Q1 " + (YEAR(Date@row) + 1), IF(MONTH(Date@row) = 1, "Q1 " + YEAR(Date@row), IF(MONTH(Date@row) > 7, "Q4 " + YEAR(Date@row), IF(MONTH(Date@row) > 4, "Q3 " + YEAR(Date@row), "Q2 " + YEAR(Date@row)))))
Hope this helps!
-
PERFECT! That worked exactly the way it was intended! Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!