Calculating the quarter complete

Edward Townsend

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.

Adrian Mandile CHESS

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"

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?

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!

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?

Adrian Mandile CHESS

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"

Edward Townsend

https://community.smartsheet.com/discussion/comment/301330#Comment_301330

YES! Thanks for the formula, it worked beautifully!