Need Calculation - If A1=Annual, Anniv. Date for Current Year, else Biennial Date

I am trying to calendar required filings on 1 Sheet, if possible. Thank you in advance for sharing your wisdom!

Filings are calculated from the First Date. Some filings have annual First Date anniversary Due Dates and others, problematically, are due every other year (Biennially). I would like the calculation to show the next Due Date and automatically update year to year. As another wrinkle, filing window opens 5 months before Due Date, we use this date as the trigger so it will be helpful to calculate this as well.

Sheet formatted as table, components as follows (no committed to column location of reorganization will make clear):

$A2= Annual/Biennial Filing Requirement;
$D2= First Date (base date for calculation);
$E2= Filing Window opens - 5 month's before filing date, currently using the following (suppose this could be Filing Due Date - 5 months, but I lack knowledge of this as well):

Please Login or Register  to view this content.

$G2= Filing Due Date, currently using the following

Please Login or Register  to view this content.

This works great for the Annual Filings, but not so for Biennial.

I am thinking it will involve something like:

Please Login or Register  to view this content.

Attached is what I think might work for you. Wasn't sure if I followed what you wanted to accomplish exactly.

Great, chea12. Thank you for taking a crack at this. You attempt is substantially what I am looking for, but my problem with the biennial filings persist. For instance, your clean solution gets me the proper date for the first Biennial filing, but 4 years from how it will show an old date, for example I backdated 2 columns from your attempt with the following result:

Renewal_Type_____First_Date_____Filing_Opens_____Last_Day
Annual___________10/8/2012______5/11/2012_______10/8/2013
Annual___________1/1/2013_______8/4/2012________1/1/2013
Biennial__________3/4/2010_______10/5/2009_______3/3/2012
Biennial__________7/22/2011_______2/22/2011_______7/21/2013

Originally Posted by cheal2

Attached is what I think might work for you. Wasn't sure if I followed what you wanted to accomplish exactly.

Alright, this should rectify that problem.

I added an additional column 'Renewal' to designate how many times someone has renewed already. Important part to determining when filing should open.

Thank you. Works as required!

Originally Posted by cheal2

Alright, this should rectify that problem.

I added an additional column 'Renewal' to designate how many times someone has renewed already. Important part to determining when filing should open.