IF Function to Add Quarter and Year
Hello,
I am editing a smartsheet that tracks tasks. I have a column for the quarter that needs the year based off [End Date]@row for all tasks added after the calculated quarter.
I am using the formula:
=IF(ISDATE([End Date]1), IF(MONTH([End Date]1) <= 3, "Q1", IF(MONTH([End Date]1) <= 6, "Q2", IF(MONTH([End Date]1) <= 9, "Q3", "Q4"))))
How can I add something that references YEAR([End Date]1) after the "Q#" in Smartsheet without having to make several "Reference" columns?
Ex.: Q2 2024
I am not the owner of the sheet and don't have high permission to add columns.
Answers
-
All,
I figured it out. You can do text and references/formulas in the result of a cell using "+". For this question I would need to change the true result of each IF statement from "Q#" to "#Q " + YEAR([End Date]1). I was then requested to lower the Year down to the last two digits; I am able to do that using the RIGHT function: RIGHT(YEAR([End Date]1),2).
The final formula now looks like:
=IF(ISDATE([End Date]1), IF(MONTH([End Date]1) <= 3, "1Q " + RIGHT(YEAR([End Date]1), 2), IF(MONTH([End Date]1) <= 6, "2Q " + RIGHT(YEAR([End Date]1), 2), IF(MONTH([End Date]1) <= 9, "3Q " + RIGHT(YEAR([End Date]1), 2), "4Q " + RIGHT(YEAR([End Date]1), 2)))))
And gives results like: 2Q 24
Hopefully this saves someone time attempting to do the same or similar functions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!