Need assistance with a Date Formula

I am needing assistance with a DATE formula that will compare a date and return a report year and payment year based on certain cut-off criteria. I have provided a sample spread sheet to help guide you through the process. As a matter of background, let’s assume there are five basic cut-off and reporting cycles as follows:

Cut-off date Report Date:
6/30 10/31 or 11/1
12/31 3/1, 3/10, 5/1
3/1/ 7/1
3/31 7/1

California is an anomaly, as it has a 6/30 cut-off, a report date of 10/31 following that 6/30 but a payment date of 6/15 the following year.

Using the CHECK date, the formula is designed to return a “Dormancy” date in column “E”. Once the dormancy date is known I want the formula to then provide a report year and payment date. When the cut-off date is June 30th of a given year, the report date is either October 31/November 1 following that June 30th. The formula I am using appears to return the proper results consistently under the above scenario. However, the formula tends to run a foul where the state of Texas is concerned. Texas has a cut-off date March 1st and a report date of July 1st following that March 1st. For whatever reason, when the check date falls right around the cut-off date (column “I”), the formula gives a false report year as evidenced by cell F17, which returned a “Report Year” of 2016 but should have returned 2017.

The second issue is getting a formula to return the correct “Payment Year” (column “G”). For all transactions with cut-off dates (Column “I”) of 6/30 the formula should return a value of the year of the dormancy date in column “E”. If you look at cell “G2” you note that the results is 2017 but should be 2015, for cell “G#” the formula returned a value of 2017 but should returned 2016. Cell “G4” did return the proper value of 2017 because the dormancy date was past the 6/30 cut-off date. If you note cell “G11” the value returned was 2017 but should have returned 2016. As you move down the worksheet to the other categories, you will note the inconsistencies. I am not sure if the actual year in the columns I and J have anything to do with the return of a false value as it picks-up those values from the “Reporting Table” (L1:V61). Since this table must be used every year I was trying to avoid having to change the year in the “Cut-off” and “Report” columns and only enter month/day but I had problems with other formulas. Perhaps you can also shed some light on this issue also. I thank you for any insight you can provide.