Identify Quarters based on date AND add text if cell is not a date

StefanieM

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

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.

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:

=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.