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
Check out the Formula Handbook template!