Inserting Quarter and Year for fiscal year (July-June)
Tried to use other formulas on here, but can't seem to get it to work. Our fiscal year is June - July. Trying to have a column show Q1 - FY22 if a date referenced is July - Sept 2021, Q2 - FY22 if a date referenced is Oct - Dec 2021, etc. The column I want to read against is "Completed Date" and both columns (Completed Date and Qtr Yr Complete) are formatted for date.
Any help is appreciated.
Answers
-
Hi @jason.scott@sclogistics.com
The Qtr Yr Complete column where the formula is input should be a Text/Number column. Then we can use the MONTH and YEAR functions to find out what month the date is in, then return the appropriate quarter.
It will be an IF statement per quarter, so here's one to show you the structure:
Q1 - FY22 = July - Sept 2021
First we check the Year, then we check the MONTH for each option (7, 8, or 9).
=IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 7, MONTH([Completed Date]@row) = 8, MONTH([Completed Date]@row) = 9)), "Q1 - FY22")
So now we can add together each of the Quarter options:
=IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 7, MONTH([Completed Date]@row) = 8, MONTH([Completed Date]@row) = 9)), "Q1 - FY22", IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 10, MONTH([Completed Date]@row) = 11, MONTH([Completed Date]@row) = 12)), "Q2 - FY22", IF(AND(YEAR([Completed Date]@row) = 2022, OR(MONTH([Completed Date]@row) = 1, MONTH([Completed Date]@row) = 2, MONTH([Completed Date]@row) = 3)), "Q3 - FY22", IF(AND(YEAR([Completed Date]@row) = 2022, OR(MONTH([Completed Date]@row) = 4, MONTH([Completed Date]@row) = 5, MONTH([Completed Date]@row) = 6)), "Q4 - FY22", "Not FY22"))))
What do you want it to return if you get into FY23? I've added a quick "Not FY22" if it's an earlier or later date. Let me know if this works for you!
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
- 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
Check out the Formula Handbook template!