Calcuate Expiry Date based on multiple cirteria #2

[sorensjp]

Hello All,

I requested assistance on a Formula a month ago and realized some items I over looked. I was kindly assisted by Bo_Ry and he created the Formula written below.

I'm currently using this Formula for "Date of Expiration" as '=IF(D2="","",MIN(MAX(EDATE(MAX(D2:F2),8+2*(G2="yes")),EDATE(H2,4)),EDATE(MAX(D2:F2),12)))
But I need to adjust the formula to include whether the PQS is "Completed" and "Sustainment Fire" limit dates are only from 6-8 months from Initial "Full Course of Fire"

ORIGINAL POST
My goal is to determine a Qualification Expiration Date based on multiple criteria. The Navy requires an annual weapons Live Fire qualification to include:
PQS (Personal Qualification Standard)
Date of NHQC (Naval Handgun Qualification Course)
Date of HPWC (Handgun Practical Weapons Course)
Date of HLLC (Handgun Low-Light Course)
If cells "C:F" then Cell "I" will reflect 8 months the latest date in cells "D:F". If the 2 Month Extension is has "Yes" then add two months to the Expiration Date in Cell "I". The Sustainment (Cell "H") can extend the Annual COF an additional 4 months but to a limit date of one year from the initial live Fire Qualification Course.

Full Course of Fire = NHQC+HPWC+HLLC

Column C = PQS
Column D = Date of NHQC
Column E = Date of HPWC
Column F = Date of HLLC
Column G = 2Mo Extension
Column H = Sustainment Fire (Extends the Qualification an additional 4 months to no more than 1 year from Full Course of Fire).
Column I = Date of Expiration

The attached file includes sample data with varied dates. Thank you for your time and attention in addressing this.

V/r,
Jim