• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expiration of dates (qualifications) with multiple criteria

sorensjp

New Member
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
 

Attachments

Top