months to quarter - odd FY
I'm trying to create a formula to associate months with a quarter - and it's not standard fiscal quarter. so I was thinking of doing an If/or and clearly identifying which months, but I can't figure out syntax.. any help appreciated - or any other ideas?
Answers
-
What are the months for each quarter?
-
Sept, Oct, Nov = Q1
Dec, Jan, Feb = Q2
and so on
-
Try this:
="Q" + IF(OR(MONTH([Date Column]@row) <= 2, MONTH([Date Column]@row) = 12), "2", IF(MONTH([Date Column]@row) <= 5, "3", IF(MONTH([Date Column]@row) <= 8, "4", "1")))
-
Thank you for your help, but I have another complication/clarifier. I have 2 columns for the 'date' listing the month and year separately as the forecast date may span years. So, I have 'month' listed as text (January, February, etc) and year listed (FY22 or FY23) [see below]. I could change the year to just read 2022 or 2023 and we can change the output of the formula to read FY of that works... thoughts?
-
actually, I just added (and hid) a date column that I used as a reference point. To be fully transparent for folks that might use this, then I changed the [date column] part of the formula to just date@row as the title of the column was just "date". All set. Thank you!
-
Happy to help. 👍️
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!