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"))))

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@StefanieM

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)))))))

StefanieM

This is perfect. Thank you so much.

All comments

Mike TV

@StefanieM

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))))))

Mike TV

@StefanieM

Oops. I didn't quite read your post entirely. 1sec I can change it some.

Mike TV

@StefanieM

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:

Quarter and Year formula:

StefanieM

This is perfect. Thank you so much.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.