Semi-Dynamic Fiscal Year & Quarter Formula

[legalhustler]

I have a workbook with the following dynamic formula that determines the fiscal year and quarter based on todays date. When I run the formula today it will result in "FY 2019 Q2" but when I open the same workbook in April 2019 it will change to "FY 2019 Q3" but I do not want that to happen when the window for that fiscal year and quarter changes, essentially making the formula from fully dynamic to semi-dynamic. How can I achieve this?

="FY "&YEAR(TODAY())-(MONTH(TODAY())<10)&" "&LOOKUP(MONTH(TODAY()),{1,"Q2";4,"Q3";7,"Q4";10,"Q1"})

*Note* I do not want to paste the formula as values. I like to keep it semi-automated.

 

[Ruca13]

Instead of using TODAY in the formula, refer to another cell where you put a date. Then, unless you change the date, the result will hold.

Regards,
Rui

 

[shg]

That formula returns FY 2018 Q2 today.

With the date of interest in A2,

="FY " & YEAR(A2) + (MONTH(A2) > 9) & " Q" & LOOKUP(MONTH(A2), {1,4,7,10}, {2,3,4,1})