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

Options
StefanieM
StefanieM ✭✭
edited 09/30/22 in Formulas and Functions

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

Tags:

Best Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    StefanieM ✭✭
    Answer ✓
    Options

    This is perfect. Thank you so much.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @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
    Mike TV ✭✭✭✭✭✭
    Options

    @StefanieM

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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    StefanieM ✭✭
    Answer ✓
    Options

    This is perfect. Thank you so much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!