Need help on Formula

Options

I am writing this formula in column to calculate the Quarter (Q1,Q2,Q3,Q4) from a date and getting results like (Q1,Q2,Q3,Q4) without the year. Can someone please help me with getting this value Q4 - 2022, Q1- 2023 , Q2 - 2023 etc.

Thanks in advance!


=IFERROR(IF(MONTH([Reporting Start Date]@row) < 4, "Q1", IF(MONTH([Reporting Start Date]@row) < 7, "Q2", IF(MONTH([Reporting Start Date]@row) < 10, "Q3", "Q4"))), "")

Tags:

Best Answer

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓
    Options

    @Parvez Khan

    You could use something like this:

    =IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")

    Hope this helps!

Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓
    Options

    @Parvez Khan

    You could use something like this:

    =IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")

    Hope this helps!

  • Parvez Khan
    Options

    Thanks a lot Kelly. It is now working as expected. One more question, when I changed the Date to Reporting Start Date this is not working. I am now using the below query. Not sure if the issue is the spaces in the "Reporting Start Date" column.

    =IFERROR(IF(OR(MONTH(Reporting Start Date@row) = 1, MONTH(Reporting Start Date@row) = 2, MONTH(Reporting Start Date@row) = 3), "Q1" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 4, MONTH(Reporting Start Date@row) = 4, MONTH(Reporting Start Date@row) = 6), "Q2" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 7, MONTH(Reporting Start Date@row) = 8, MONTH(Reporting Start Date@row) = 9), "Q3" + " " + YEAR(Reporting Start Date@row), IF(OR(MONTH(Reporting Start Date@row) = 10, MONTH(Reporting Start Date@row) = 11, MONTH(Reporting Start Date@row) = 12), "Q4") + " " + YEAR(Reporting Start Date@row)))), "")

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Options

    @Parvez Khan

    You'll need to put brackets around the column names (column names with spaces or digits need brackets).

    =IFERROR(IF(MONTH([Reporting Start Date]@row) = 1 etc.

    Does that work for you?

  • Parvez Khan
    Options

    Thank you once again for your help. It is working as expected!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!