Hello all,
I'm trying to adjust this Formula for the "Qualification Expiration":
=IF(D2="","",MIN(MAX(EDATE(MAX(D2:F2),8+2*(G2="yes")),EDATE(H2,4)),EDATE(MAX(D2:F2),12)))
Thank you Bo_Ry for the formula above.
The goal is to include whether the PQS is "Completed" (in column “C”). More importantly I need "Sustainment Fire" date sets the "Qualification Expiration" date to 1 Full Year after the initial Qualification Shoot. Here are the limits/requirements:
Requirements IAW Navy instruction
Initial Qualification (Good for 8 Months)
The Navy Live Fire initial qualification requires the following:
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)
2-month Extention
Extends the Expiration Date 2 months if unable to complete due to Operational commitments. (Yes, or Blank in cell “I”)
Sustainment Fire
Extends the Qualification to a full Year from the Initial Qualification Date may be conducted anytime prior to the initial 8 Month expiration date. The “2 Month Extension” has no effect on the 1 Year limit but can allow for the Sustainment Shoot to be conducted pass the initial Qualification Expiration Date.
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
V/r,
Jim
I'm trying to adjust this Formula for the "Qualification Expiration":
=IF(D2="","",MIN(MAX(EDATE(MAX(D2:F2),8+2*(G2="yes")),EDATE(H2,4)),EDATE(MAX(D2:F2),12)))
Thank you Bo_Ry for the formula above.
The goal is to include whether the PQS is "Completed" (in column “C”). More importantly I need "Sustainment Fire" date sets the "Qualification Expiration" date to 1 Full Year after the initial Qualification Shoot. Here are the limits/requirements:
Requirements IAW Navy instruction
Initial Qualification (Good for 8 Months)
The Navy Live Fire initial qualification requires the following:
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)
2-month Extention
Extends the Expiration Date 2 months if unable to complete due to Operational commitments. (Yes, or Blank in cell “I”)
Sustainment Fire
Extends the Qualification to a full Year from the Initial Qualification Date may be conducted anytime prior to the initial 8 Month expiration date. The “2 Month Extension” has no effect on the 1 Year limit but can allow for the Sustainment Shoot to be conducted pass the initial Qualification Expiration Date.
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
V/r,
Jim