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
-
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
-
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!
-
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
-
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 -
YES! Thanks for the formula, it worked beautifully!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
