Calculating the quarter complete

I have a formula that calculates the Quarter complete. It worked great at the end of last year, but now I have some programs that will go into 2022, and I need to differentiate between Q3/Q4 of 21 and Q3/Q4 of 22. I thought by using the YEAR function that would help, but alas I get the dreaded syntax error:


=IF(ISDATE([Target end]@row), IF(MONTH([Target end]@row) <= 3, "Q1-22", IF(MONTH([Target end]@row) <= 6, "Q2-22", IF(MONTH([Target end]@row) <= 9, "Q3-21", IF(MONTH([Target end]@row) <= 12, "Q4-21"), IF(Year([Target end]@row) <= 22, "Q4-22"))))))


Any help would be greatly appreciated.

Best Answer

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    edited 08/12/21 Answer ✓

    Hi @Edward Townsend,

    I assume you are working with calendar year quarters (different formula for July-June Financial Years).

    Have you tried just calculating the label using the YEAR() of your 'Target end' date instead of hard-coding it? So for dates of August 1 2021, October 10 2021, January 3 2022, try:

    =IFERROR("Q" + IF(MONTH(Date@row) > 9, 4, IF(MONTH(Date@row) > 6, 3, IF(MONTH(Date@row) > 3, 2, 1))) + "-" + RIGHT(YEAR(Date@row), 2), "")

    ="Q3-21", "Q4-21", "Q1-22"

    However, if you want 'naturally ordered' labels, try swapping the Year and Quarter order:

    =IFERROR(RIGHT(YEAR([Target end]@row), 2) + "Q" + IF(MONTH([Target end]@row) > 9, 4, IF(MONTH([Target end]@row) > 6, 3, IF(MONTH([Target end]@row) > 3, 2, 1))), "")

    ="21Q3", "21Q4", "22Q1"

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Edward,

    If possible, you may want to think about setting up a separate table of end-of-quarter dates and related Q#-YY. You could then use combined INDEX, MATCH functions?

    Otherwise, if still using the above formula, I'd look to add the AND & OR function, such that

    =IF(ISDATE([Target end]@row), IF( AND ( Year([Target end]@row) = 22, MONTH([Target end]@row) <= 3, "Q1-22", IF( OR (MONTH([Target end]@row) <= 6, MONTH([Target end]@row) '>3), "Q2-22", etc.

    However, you can tell this gets very messy and would need updating.

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Thanks for the feedback, you're definitely on point about it getting messy. I'm assuming that I'd need to create a separate sheet to do the quarter lookup in the alternative example you provided. I'd much rather something I could use across my sheets than that clunky formula.

    Could you provide the steps to the separate table solution?


    Thanks so much for the assistance!

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Edward,

    I would review Smartsheet's article here as a start (and then a bit more research within Community).

    Suffice to say, I'd have the separate table set up with (minimum) 2 columns - Date | Qtr.

    The date would correspond to the start of the Qtr, with the Qtr column reflecting the text (e.g. Q1-22)

    The number of rows would cover at least two to four years, depending on your requirements.

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    edited 08/12/21 Answer ✓

    Hi @Edward Townsend,

    I assume you are working with calendar year quarters (different formula for July-June Financial Years).

    Have you tried just calculating the label using the YEAR() of your 'Target end' date instead of hard-coding it? So for dates of August 1 2021, October 10 2021, January 3 2022, try:

    =IFERROR("Q" + IF(MONTH(Date@row) > 9, 4, IF(MONTH(Date@row) > 6, 3, IF(MONTH(Date@row) > 3, 2, 1))) + "-" + RIGHT(YEAR(Date@row), 2), "")

    ="Q3-21", "Q4-21", "Q1-22"

    However, if you want 'naturally ordered' labels, try swapping the Year and Quarter order:

    =IFERROR(RIGHT(YEAR([Target end]@row), 2) + "Q" + IF(MONTH([Target end]@row) > 9, 4, IF(MONTH([Target end]@row) > 6, 3, IF(MONTH([Target end]@row) > 3, 2, 1))), "")

    ="21Q3", "21Q4", "22Q1"

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!