Assigning a Fiscal Quarter to Maturity

[shaero]

Hello Forum,

Was hoping that you guys could help me out with this problem. Basically, I'm trying assign a fiscal quarter to each maturity. For example, if the asset matured in 1/31/2010 it should be tagged as Q1 2010 or if it is maturing in 10/1/2013 then it should be tagged as Q4 2013.

This is what I've got so far. For the maturity column I had a lookup function gathering all the maturities. I copied and pasted the entire column so that it would only have values and then transformed it using text to columns. Then I used the function right to pull the year from the maturity and function left to pull the date (meaning that I had to create additional columns). Lastly, I used this function in order to assort the years and months into fiscal quarters: =CONCATENATE(IF(AND(L2>=Sheet2!$A$1,L2<=Sheet2!$A$2),"Q1",IF(AND(L2>=Sheet2!$B$1,L2<=Sheet2!$B$2),"Q2",IF(AND(L2>=Sheet2!$C$1,L2<=Sheet2!$C$2),"Q3",IF(AND(L2>=Sheet2!$D$1,L2<=Sheet2!$D$2),"Q4",0))))," ",M2)

Where L2 is the date, M2 is the year and Sheet2 is just a listing of all the quarters. Theres a couple of issues with this. First off, the formula does not correctly assign maturities that are in the fourth quarter. For example, the previous example that I used where the mat is 10/1/2013 would be tagged as Q1 2013 and not Q4 2013. Also, I wanted to keep the lookup column which refreshes the maturities without transforming it to text.

Any ideas?

Thanks in advance,
shaero

 

[VoG]

Hello. Perhaps like this where L2 contains a date

="Q"&LOOKUP(MONTH(L2),{1,4,7,10},{1,2,3,4})&" "&YEAR(L2)

 

[shaero]

This is brilliant. Thank you!