[Fiscal Year Quarters] Formula that returns the fiscal year for a date

Hello everyone,

I'm in need of some help regarding a formula that returns the Fiscal Year Quarter from a date, so far what I've done is write in a sheet the start and end dates of 2020 Quarters and used a nested if function to bring back a correct result.

I work with a lot of data and I may get requests to find out the Fiscal Year Quarters for other dates, ex : 2017 / 2016 etc.

Is there a formula that directly looks at a date and returns the exact quarter and fiscal year ?

Thank you !

Attached you can find my document with the nested formulas.

and/or another option:

=LOOKUP(MONTH(A2);{1,4,7,10};"FY"&RIGHT(YEAR(A2)+{0,1,1,1},2)&" Q"&{4,1,2,3})
copied down

swap out ; for , if not required per your locale.

note: as PaulM has pointed out, you may want to apply an outer error handler for non-dates (e.g. Spring)

Thank you all !

They all worked perfectly.

Thank you XLent,

I am however assigned to make the formula more simplistic in terms of the entry and I found that using the Choose function is accepted at my workspace and now all I have to do is add the Year near the Quarter result which at the time of writing this I am still having issues.

OK, but in the most basic of terms, logically, you cannot determine year ignorant of month as the latter determines the former
i.e. for months 1-3 Calendar Year = Fiscal Year whereas for all other months Fiscal Year = Calendar Year + 1

You can write this basic logic as many different ways as you like, and CHOOSE vs LOOKUP is inconsequential, both are on the beginner side of things

to use CHOOSE to determine year you would need:

=YEAR(x) + CHOOSE(MONTH(x),0,0,0,1,1,1,1,1,1,1,1,1)

which is clearly ridiculous - a basic IF approach would do the same:

=YEAR(x) + IF(MONTH(x)>3,1,0)

(the above is for transparency as technically the IF w/ 1 & 0 isn't required)

this will still return a 4 digit year, obviously, so to get FY00 you need to either, replace the first 2 chars of the year

=REPLACE(< year calc >,1,2,"FY")

or, use RIGHT to get 2 chars and prefix with FY

="FY"&RIGHT(< year calc >,2)

to the above you can append your existing CHOOSE with a "Q" prefix to get "FY## Q#"

if the above does not work for you I will let others pick up as I'm not quite sure how simpler you can make it and, in reality, you cannot do this with a solitary CHOOSE call.

I understand and appreciate the help, I've managed to apply some of the things you mention and the formula looks like this - ="FY"&RIGHT(YEAR(A2),2)&" Q"&CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3).

While it does give me the correct quarter now and the returned result has the aforementioned format it does not five me the correct fiscal year

For example if I use it on FY19 Q3, let's say the start 1-Oct-2018, the result will be FY18 Q3.

Thank you all !