# Formula to Find the Start/End Timeline

Options
✭✭✭✭✭

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.

Tags:

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭