Formula to Find the Start/End Timeline
Hello SS Community,
I'm trying to find a formula to insert in the sheet summary that brings the earliest Quarter and Fiscal Year (FY) and the last/furthest quarter and FY.
Thank you in advance.
Best Answers

My apologies. I had a misplaced parenthesis. Try this corrected one:
="FY" + LEFT(MIN([Start Helper]:[Start Helper]), 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))
And yes. It should be 3 digits that represent "YYQ"

@maltaee You could try something like this:
="FY" + RIGHT(IF(TODAY()> DATE(YEAR(TODAY()), 7, 1), YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + " Q" + IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), "2", IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), "1", IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), "4", "3")))
Answers

You'll need to insert two helper text/number columns (that can be hidden after setting everything up).
The first would be a helper for the Start:
=VALUE(RIGHT([Start FY]@row, 2) + "" + RIGHT([Start Quarter]@row))
The second would be a helper for the End. You would use the same formula as above except reference the end fy and quarter columns.
Then the earliest FY/Quarter would be:
="FY" + LEFT(MIN([Start Helper]:[Start Helper], 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))
The latest FY/Quarter would be the same as above but reference the End Helper and switch the MIN functions to MAX functions.

Thanks so much. I'm getting "#Invalid Operation" in the sheet summary.
Does the Start Helper pull the right thing? it shows 3 digits.

My apologies. I had a misplaced parenthesis. Try this corrected one:
="FY" + LEFT(MIN([Start Helper]:[Start Helper]), 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))
And yes. It should be 3 digits that represent "YYQ"

Sorry to bother you. It still shows an invalid operation.

Do you have any blanks? If so, try...
Wrap the helper column formulas in an IFERROR like so:
=IFERROR(VALUE(RIGHT([Start FY]@row, 2) + "" + RIGHT([Start Quarter]@row)), "")
And adjust the final formula like this:
="FY" + LEFT(MIN(COLLECT([Start Helper]:[Start Helper], [Start Helper]:[Start Helper], @cell <> "")), 2) + " " + "Q" + RIGHT(MIN(COLLECT([Start Helper]:[Start Helper], [Start Helper]:[Start Helper], @cell <> "")))

Paul,
Thanks for catching that. Actually, there are TBD, not blanks, in many cells. I have to wait until they change it unless there is a workaround.

That would end up throwing the same error, so the IFERROR and COLLECT fixes above should still do the trick for you.

Thanks, Paul.
It worked after changing the TBDs cells.
Last question. Is there a formula to display the current quarter and fiscal year? Ex. It should show FY23 Q2 now until Dec 31, 2022 and then FY23 Q3

@maltaee You could try something like this:
="FY" + RIGHT(IF(TODAY()> DATE(YEAR(TODAY()), 7, 1), YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + " Q" + IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), "2", IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), "1", IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), "4", "3")))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 409 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!