Formula to Find the Start/End Timeline

maltaee

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.

Paul Newcome

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"

Paul Newcome

@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")))

Paul Newcome

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.

maltaee

Thanks so much. I'm getting "#Invalid Operation" in the sheet summary.

Does the Start Helper pull the right thing? it shows 3 digits.

Paul Newcome

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

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

Paul Newcome

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 <> "")))

maltaee

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.

Paul Newcome

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

maltaee

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

Paul Newcome

@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")))