Calculating the quarter complete

Edward Townsend
Edward Townsend ✭✭✭
in Formulas and Functions

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:


Screen Shot 2021-08-11 at 2.23.43 PM.png

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

· Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter
  • Edward Townsend
    Edward Townsend ✭✭✭

    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!

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • Edward Townsend
    Edward Townsend ✭✭✭
    https://community.smartsheet.com/discussion/comment/301330#Comment_301330

    YES! Thanks for the formula, it worked beautifully!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!