Need help on Formula
I am writing this formula in column to calculate the Quarter (Q1,Q2,Q3,Q4) from a date and getting results like (Q1,Q2,Q3,Q4) without the year. Can someone please help me with getting this value Q4 - 2022, Q1- 2023 , Q2 - 2023 etc.
Thanks in advance!
=IFERROR(IF(MONTH([Reporting Start Date]@row) < 4, "Q1", IF(MONTH([Reporting Start Date]@row) < 7, "Q2", IF(MONTH([Reporting Start Date]@row) < 10, "Q3", "Q4"))), "")
Best Answer
-
You could use something like this:
=IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")
Hope this helps!
Answers
-
You could use something like this:
=IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")
Hope this helps!
-
Thanks a lot Kelly. It is now working as expected. One more question, when I changed the Date to Reporting Start Date this is not working. I am now using the below query. Not sure if the issue is the spaces in the "Reporting Start Date" column.
=IFERROR(IF(OR(MONTH(Reporting Start Date@row) = 1, MONTH(Reporting Start Date@row) = 2, MONTH(Reporting Start Date@row) = 3), "Q1" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 4, MONTH(Reporting Start Date@row) = 4, MONTH(Reporting Start Date@row) = 6), "Q2" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 7, MONTH(Reporting Start Date@row) = 8, MONTH(Reporting Start Date@row) = 9), "Q3" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 10, MONTH(Reporting Start Date@row) = 11, MONTH(Reporting Start Date@row) = 12), "Q4") + " " + YEAR(Reporting Start Date@row)))), "")
-
You'll need to put brackets around the column names (column names with spaces or digits need brackets).
=IFERROR(IF(MONTH([Reporting Start Date]@row) = 1 etc.
Does that work for you?
-
Thank you once again for your help. It is working as expected!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!