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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!