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, "Q122", IF(MONTH([Target end]@row) <= 6, "Q222", IF(MONTH([Target end]@row) <= 9, "Q321", IF(MONTH([Target end]@row) <= 12, "Q421"), IF(Year([Target end]@row) <= 22, "Q422"))))))
Any help would be greatly appreciated.
Best Answer

Hi @Edward Townsend,
I assume you are working with calendar year quarters (different formula for JulyJune Financial Years).
Have you tried just calculating the label using the YEAR() of your 'Target end' date instead of hardcoding 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), "")
="Q321", "Q421", "Q122"
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"
Answers

Hi Edward,
If possible, you may want to think about setting up a separate table of endofquarter 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, "Q122", IF( OR (MONTH([Target end]@row) <= 6, MONTH([Target end]@row) '>3), "Q222", 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!

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. Q122)
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

Hi @Edward Townsend,
I assume you are working with calendar year quarters (different formula for JulyJune Financial Years).
Have you tried just calculating the label using the YEAR() of your 'Target end' date instead of hardcoding 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), "")
="Q321", "Q421", "Q122"
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"

YES! Thanks for the formula, it worked beautifully!
Help Article Resources
Categories
Check out the Formula Handbook template!