List all the fiscal years and quarters from two dates

Question

Good afternoon!

I'm in excel and i was wondering if there is a way to get a list of all of the fiscal years and quarters covered between two certain dates.

I have award start and end date fields. for example:

start date: 7/6/2012
end date: 10/7/2016

Is there any way to get an output of all the fiscal years and quarters covered? like this:

FY13 Q1
FY13 Q2, FY13 Q3, FY13 Q4, FY14 Q1, FY14 Q2, FY14 Q3, FY14 Q4, FY15 Q1, FY15 Q2, 
FY15 Q3, FY15 Q4, FY16 Q1, FY16 Q2, FY16 Q3, FY16 Q4, FY17 Q1

you may need to create a list with start and end of each fiscal year and then add a column comparing start and end with the given dates that returns something like "IN", "OUT" or "Part IN" depending on the result — Jayvee
I don't see anyway to do this using formulas without either helper columns or rows; or arrays and some type of array concatenation. What version of Excel are you using? If you don't have an Excel that has TEXTJOIN() is VBA a viable option? Lastly please share what you have done so far. Remember that SO is not a code writing service but rather a forum to get targeted help on errors in your code. — nbayly

EEM EEM · Accepted Answer · 2016-10-13T23:56:53

Let's establish first the following Month\Year-Quarter Relationship based on Fiscal Year End:

Assuming the Start and End dates are located at B2:C3 and the results by Fiscal Year End at C5:F25 enter the following formulas:

Fiscal Year End - March

in C7:

=IF($C$3<$C$2,"!Err",
CONCATENATE("FY",
LOOKUP(MONTH($C$2),{1,4},{0,1})+TEXT($C$2,"yy"),
" Q",LOOKUP(MONTH($C$2),{1,4,7,10},{4,1,2,3})))

in C8:C25:

=IF(OR(EXACT(C7,""),
EXACT(C7,
CONCATENATE("FY",
LOOKUP(MONTH($C$3),{1,4},{0,1})+TEXT($C$3,"yy"),
" Q",LOOKUP(MONTH($C$3),{1,4,7,10},{4,1,2,3})))),"",
CONCATENATE("FY",
MID(C7,3,2)+IF(RIGHT(C7,1)-4=0,1,0),
" Q",CHOOSE(RIGHT(C7,1),2,3,4,1)))

Fiscal Year End - June

in D7:

=IF($C$3<$C$2,"!Err",
CONCATENATE("FY",
LOOKUP(MONTH($C$2),{1,7},{0,1})+TEXT($C$2,"yy"),
" Q",LOOKUP(MONTH($C$2),{1,4,7,10},{3,4,1,2})))

in D8:D25:

=IF(OR(EXACT(D7,""),
EXACT(D7,
CONCATENATE("FY",
LOOKUP(MONTH($C$3),{1,4},{0,1})+TEXT($C$3,"yy"),
" Q",LOOKUP(MONTH($C$3),{1,4,7,10},{3,4,1,2})))),"",
CONCATENATE("FY",
MID(D7,3,2)+IF(RIGHT(D7,1)-4=0,1,0),
" Q",CHOOSE(RIGHT(D7,1),2,3,4,1)))

Fiscal Year End - September

in E7:

=IF($C$3<$C$2,"!Err",
CONCATENATE("FY",
LOOKUP(MONTH($C$2),{1,10},{0,1})+TEXT($C$2,"yy"),
" Q",LOOKUP(MONTH($C$2),{1,4,7,10},{2,3,4,1})))

in E8:E25:

=IF(OR(EXACT(E7,""),
EXACT(E7,
CONCATENATE("FY",
LOOKUP(MONTH($C$3),{1,10},{0,1})+TEXT($C$3,"yy"),
" Q",LOOKUP(MONTH($C$3),{1,4,7,10},{2,3,4,1})))),"",
CONCATENATE("FY",
MID(E7,3,2)+IF(RIGHT(E7,1)-4=0,1,0),
" Q",CHOOSE(RIGHT(E7,1),2,3,4,1)))

Fiscal Year End - December

in F7:

=IF($C$3<$C$2,"!Err",
CONCATENATE("FY",
TEXT($C$2,"yy"),
" Q",LOOKUP(MONTH($C$2),{1,4,7,10},{1,2,3,4})))

in F8:F25:

=IF(OR(EXACT(F7,""),
EXACT(F7,
CONCATENATE("FY",TEXT($C$3,"yy"),
" Q",LOOKUP(MONTH($C$3),{1,4,7,10},{1,2,3,4})))),"",
CONCATENATE("FY",
MID(F7,3,2)+IF(RIGHT(F7,1)-4=0,1,0),
" Q",CHOOSE(RIGHT(F7,1),2,3,4,1)))

List all the fiscal years and quarters from two dates

1 Answers