Fiscal Quarter and year return on formula
Hello,
I reached out yesterday about a formula to help me return the fiscal year and quarter based on the date column. My fiscal year started November 1st.
- The formula provided to me was =IF(ISDATE(Date@row), "FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) >= 11), 1, IF(MONTH(Date@row) = 2, 2, IF(MONTH(Date@row) = 3, 2, IF(MONTH(Date@row) = 4, 2, IF(MONTH(Date@row) = 5, 3, IF(MONTH(Date@row) = 6, 3, IF(MONTH(Date@row) = 7, 3, 4))))
- I entered the formula and it gives me a return of #UNPARSEABLE. Also, it is returning the value on a different column than where I entered the formula and I can't enter the formula on the date column since it is just a date column and it won't let me. attached is a picture of what my set up looks like so you can get a better understanding of what I need.
Answers
-
I believe I've been helping you on this thread, here!
Here's the updated formula with November as the start of your quarter:
="Q" + IF(OR(MONTH(Date@row) >= 11, MONTH(Date@row) = 1), "1FY", IF(MONTH(Date@row) >= 8, "4FY", IF(MONTH(Date@row) >= 5, "3FY", "2FY"))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))
You should be able to copy/paste the formula at the end, as long as the Date column is titled "Date", otherwise you may need to adjust the column name in the formula.
If you get an error, can you post a screen capture with the formula open in the cell, showing if it's highlighting the correct Date column or not?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 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
Check out the Formula Handbook template!