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.

For Texas (as example):

If month in E = month in I (Texas) what do want the result to be?

You have the same situation with Minnesota (row 7) i.e month E = Month I, BUT the Year in I is less than that in E so "apparently" by default you get the correct result.

For Texas the I year is greater than then E year

This suggests you need to take the year into account so perhaps =MAX(Year(En),Year(In)) ???

Re your second Problem:

in G2

=IF(E2="","",IF(YEAR(E2)>=YEAR(I2),YEAR(I2),YEAR(I2)+1))

in G4

=IF(E4="","",IF(YEAR(E4)>YEAR(I4),YEAR(I4),YEAR(I4)+1))

You need to work out exactly what the rules are: the formulae are behaving correctly - the logic needs amending.

Thank you for the quick response. I understand your comments and you appear to understand what I am attempting to do. I did fix the formula in column "G" to read consistently so you can see the varying results achieved. By the way MI is Michigan. That said I am going to attempt to layout the rules the formulas need to follow:

1. The payment year can never be earlier than the report year;
2. for all states that have a 6/30/yy cut-off (column "I") and the check date falls on or before that 6/30/yy date, the Report Year (column "F") should be the same year as the year in the dormancy date (e.g.,column "E's" date is 5/15/2016 should return a
Report Year of 2016)and the Payment Year (column "G")should return a year equal to Report Year (column "F")
3. for all states that have a 6/30/yy cut-off (column "I") and the check date falls after 6/30/yy date, the Report Year (column "F") should be the year following the dormancy date year (e.g., column "E's" date is 7/1/2015 should return a Report Year of 2016 and a Payment Year (column "G") of 2016
4. The only exception to the above is for California, which requires a payment date of June 15th following the Report Year (column "F")
5. For those states that use a 12/31/yy Cut-off Date (column I) and varying dates for the Report Date (column "J") I would think the same logic applies as in the case of those states with a 6/30 Cut-off Date. So as long as the Dormancy Date is on or before 12 /31/yy, the Report year would be same as the year of the Dormancy Date with the Payment Date in following year in one of the Spring months (e.g., March, April, May);
6. The State of Texas (TX) and Michigan (MI) have some altogether different dates, which appear to be troublesome. A check that reaches the Dormancy Date between 3/2/yy of one year and 3/1/yy of the next year (e.g. 3/2/2013 to 3/1/2014)should return a Report
year of 2014 and a Payment Year of 2014. But you have noted from the attached spreadsheet that cell F19 returns a Report Year of 2016 and Payment Year of 2018 respectively as opposed to 2017 and 2017. I believe the state of MI will work the same as Texas.
7. Lastly, I am trying to avoid having to go into the Reporting Table and change the YY in each of the cells to reflect the most current reporting year (e.g. now that we are in 2017 I had to go into every cell in column "U" with a 12/31 cut-off and column "V" with a report year in March, April, and May update the year to 2017 to reflect the most current reporting cycle. In June I will need to go into the table and change all the 6/30 and October 31/November 1 years from 2016 to 2017. If there were a way to avoid this manual process, or automate this somehow would then render all the calculations error proof. Any suggestions?

I have attached a revised worksheet. Thank you.