If month is January set to Q1

Options

I would like to set up a formula that is based on a dropdown of the months. Q1 would be January to March. I have been looking at using the month's formula but realized that it works off of dates, not a month dropdown. Is there a formula that I can use to update the quarter based on the month from the drop-down?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Daniel,

    Try, replate date@row with the name of your date column:

    =IF(ISDATE(Date@row), IF(MONTH(Date@row) <= 3, "Q1", IF(MONTH(Date@row) <= 6, "Q2", IF(MONTH(Date@row) <= 9, "Q3", "Q4"))))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Daniel Melton
    Options

    Hi Mark,

    I have tried the formula below and am getting an unparasable error.

    =IF(ISDATE(Planned@row), IF(MONTH(Planned@row <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))))

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try, missing a paren:

    =IF(ISDATE(Planned@row), IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Daniel Melton
    Options

    It is no longer showing unparsable, but it is no longer showing any value in the cell.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try this variation. If it returns "no date" check the format of your Planned column. It needs to be a date column and the row needs an entry.

    =IFERROR(IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4")))), "no date")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Daniel Melton
    Options

    It is giving an unparsable error again.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Daniel,

    This formula works for me:

    =IF(ISDATE(Planned@row), IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))

    Confirm that your date column is title [Planned] and that it is set as a date.

    Are you in the US? If not you may need to change the commas to periods for the right syntax.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Alon Shachar
    Options

    Try this - includes the FY (that you can leave out):


    ="Q" + IF(OR([Filter Month]@row = "April", [Filter Month]@row = "May", [Filter Month]@row = "June"), "1", IF(OR([Filter Month]@row = "July", [Filter Month]@row = "August", [Filter Month]@row = "September"), "2", IF(OR([Filter Month]@row = "October", [Filter Month]@row = "November", [Filter Month]@row = "December"), "3", IF(OR([Filter Month]@row = "January", [Filter Month]@row = "February", [Filter Month]@row = "March"), "4")))) + "FY" + IF(MONTH(TODAY()) < 4, RIGHT(YEAR(TODAY()), 2), VALUE(RIGHT(YEAR(TODAY()), 2)) + 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!