# Calculating the quarter complete

Options

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.

• ✭✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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"

• Options

YES! Thanks for the formula, it worked beautifully!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!