Identify Quarters based on date AND add text if cell is not a date
I am wanting to identify a quarter based on a date and have that formula working, but I also want it to return a value of "TBD" if the Start Date is not a date AND return a value of "Dates Invalid" if the Start Date is outside of the current year, which in this report is 2023. How can I adjust the formula below to incorporate TBD or Dates Invalid into the return?
=IF(ISDATE([Start Date]@row), IF(MONTH([Start Date]@row) <= 3, "Q1", IF(MONTH([Start Date]@row) <= 6, "Q2", IF(MONTH([Start Date]@row) <= 9, "Q3", "Q4"))))
Best Answers
-
Make sure to create a Sheet Summary cell like this to identify what year the sheet should be considering is the current year:
Quarter formula:
=IF(NOT(ISDATE([Start Date]@row)), "TBD", IF(YEAR([Start Date]@row) <> [Current Year]#, "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1", IF(MONTH([Start Date]@row) < 7, "Q2", IF(MONTH([Start Date]@row) < 10, "Q3", IF(MONTH([Start Date]@row) > 9, "Q4"))))))
Quarter and Year formula:
=IF(NOT(ISDATE([Start Date]@row)), "TBD", IF(YEAR([Start Date]@row) <> [Current Year]#, "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 7, "Q2 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 10, "Q3 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) > 9, "Q4 " + YEAR([Start Date]@row)))))))
-
This is perfect. Thank you so much.
Answers
-
Quarter formula:
=IF(NOT(ISDATE([Start Date]@row)), "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1", IF(MONTH([Start Date]@row) < 7, "Q2", IF(MONTH([Start Date]@row) < 10, "Q3", IF(MONTH([Start Date]@row) > 9, "Q4")))))
Quarter and Year formula:
=IF(NOT(ISDATE([Start Date]@row)), "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 7, "Q2 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 10, "Q3 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) > 9, "Q4 " + YEAR([Start Date]@row))))))
-
Oops. I didn't quite read your post entirely. 1sec I can change it some.
-
Make sure to create a Sheet Summary cell like this to identify what year the sheet should be considering is the current year:
Quarter formula:
=IF(NOT(ISDATE([Start Date]@row)), "TBD", IF(YEAR([Start Date]@row) <> [Current Year]#, "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1", IF(MONTH([Start Date]@row) < 7, "Q2", IF(MONTH([Start Date]@row) < 10, "Q3", IF(MONTH([Start Date]@row) > 9, "Q4"))))))
Quarter and Year formula:
=IF(NOT(ISDATE([Start Date]@row)), "TBD", IF(YEAR([Start Date]@row) <> [Current Year]#, "Date Invalid", IF(MONTH([Start Date]@row) < 4, "Q1 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 7, "Q2 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) < 10, "Q3 " + YEAR([Start Date]@row), IF(MONTH([Start Date]@row) > 9, "Q4 " + YEAR([Start Date]@row)))))))
-
This is perfect. Thank you so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!