Generating Dates from Notation: Better Way?

[ra@SCM]

Basically, I'm trying to get the formula cell to spit out the date of the corresponding quarter end. The notation I'm following is F_Q__, where the first blank is one of the following set {1,2,3,4}. The second blank is the last two numbers of the fiscal year.

With the notated cell (I5 in this case) directly above the formula cell (I6 in this case), I'm using the pasted below formula to generate the date of the quarter end. For example, "F1Q12" in the notated cell should generate "6/30/2011" in the cell below (i.e., the formula cell).

=IF(LEFT(I5,2)="FY",MID(I5,3,4),DATE(IF(VALUE(MID(I5,2,1))<4,"20"&VALUE(MID(I5,4,2)-1),"20"&VALUE(MID(I5,4,2))),IF(VALUE(MID(I5,2,1))<4,6*VALUE(MID(I5,2,1))-(3*(VALUE(MID(I5,2,1))-1)),3),IF(OR(MID(I5,2,1)="3",MID(I5,2,1)="4"),31,30)))

Luckily, this works for any subsequent notated cell, granted the notation is correct:

F1Q22 yields 6/30/2021
F4Q23 yields 3/31/2023
etc., etc.

My issue:

This is a beastly formula--what is the more elegant solution?

[Cutter]

How about

=EOMONTH(DATE(2000+RIGHT(I5,2),MID(I5,2,1)*3,1),-9)

[Haseeb Avarakkan]

Or without EOMONTH?

=DATE(1999+RIGHT(I5,2),MID(I5,2,1)*3+4,0)

[ra@SCM]

These don't work because, for some reason, I can't do math operations on any reference functions like RIGHT, MID, LEFT, etc. Is this just me?

Didn't know about EOMONTH function--thanks for trying to help out.

[Cutter]

The formula works for me. Tested on the 3 examples you gave - correct results received.