Expiration Date Formula w/criteria

sorensjp

New Member
Hi,

I'm attempting to Calculate an Qualification Expiration Date. The Absolute Limit of a Handgun Qualification is 12 months from the initial Course of Fire.

My goal is to determine a Qualification Expiration Date based on multiple criteria. The Navy requires an annual weapons Live Fire qualification to include:
Date of NHQC (Naval Handgun Qualification Course), Date of HPWC (Handgun Practical Weapons Course) and 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.

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

I'm using this formula ATM:
=IF(D3="","",MIN(MAX(EDATE(MAX(D3:F3),8+2*(G3="yes")),EDATE(H3,4)),EDATE(MAX(D3:F3),12)))

The formula fails to account for the Full 12 Months from the Dates entered in Cells D:F when the Sustainment date is entered. The final expiration date Calculates 4 Months from the Sustainment Date causing the Qual to Expire early.

Rows 1-10 contain different possible situations for the formula and Column J shows what the correct date should be.
Rows 14-17 are what I was my failed attempt to achieve a solution for my issue. I've tried to add a helper cell in the attached file thinking

The attached file includes sample data and some variables. Thank you for your time and attention in addressing this.

V/r,
Jim

Expiration Formula.xlsx
BCDEFGHIJK
2PQSNHQCHPWCHLLC2 Month EXTENSIONSUSTAINMENTQUALIFICATION EXPIRATIONCorrect Expiration Date
39MMCompleted24-Jun-1824-Jun-1824-Jun-18YES14-Feb-1914-Jun-1924-Jun-19
49MMCompleted24-Jun-1824-Jun-1824-Jun-18NO14-Feb-1914-Jun-1924-Jun-19
59MMCompleted24-Jun-1824-Jun-1824-Jun-18YES24-Apr-1924-Apr-19
69MMCompleted24-Jun-1824-Jun-1824-Jun-18NO24-Feb-1924-Feb-19
79MMCompleted24-Jun-1825-Jun-1826-Jun-18YES14-Feb-1914-Jun-1926-Jun-19
89MMCompleted24-Jun-1825-Jun-1826-Jun-18NO14-Feb-1914-Jun-1926-Jun-19
99MMCompleted24-Jun-1825-Jun-1826-Jun-18YES26-Apr-1926-Apr-19
109MMCompleted24-Jun-1825-Jun-1826-Jun-18NO26-Feb-1926-Feb-19
11
12
13
14Test
15NHQCHPWCHLLC2 Month (HELPER Cell)2 Month EXTENSIONSUSTAINMENT (HELPER Cell)SUSTAINMENTQUALIFICATION EXPIRATIONCorrect Expiration Date
169MM24-Feb-1924-Feb-1924-Feb-191YESFormula Here?24-Oct-1924-Oct-19
179MM24-Feb-1924-Feb-1924-Feb-190NOFormula Here?24-Oct-1924-Oct-19
Sheet1 (2)
Cell Formulas
RangeFormula
I3:I10I3=IF(D3="","",MIN(MAX(EDATE(MAX(D3:F3),8+2*(G3="yes")),EDATE(H3,4)),EDATE(MAX(D3:F3),12)))
F16:F17F16=IF(G16="YES",1,"0")
J16J16=IF(C16="","0",MIN(MAX(EDATE(MAX(C16:E16),8+2*(F16="1")),EDATE(I16,4)),EDATE(MAX(D16:F16),12)))
J17J17=IF(C17="","0",MIN(MAX(EDATE(MAX(C17:E17),8+2*(G17="1")),EDATE(I17,4)),EDATE(MAX(D17:F17),12)))

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jorismoerings

Well-known Member
Hi,

Try this:
Book1
BCDEFGHIJ
1PQSNHQCHPWCHLLC2 Month EXTENSIONSUSTAINMENTQUALIFICATION EXPIRATIONCorrect Expiration Date
29MMCompleted24-jun-1824-jun-1824-jun-18YES14-feb-1924-jun-1924-jun-19
39MMCompleted24-jun-1824-jun-1824-jun-18NO14-feb-1924-jun-1924-jun-19
49MMCompleted24-jun-1824-jun-1824-jun-18YES24-apr-1924-apr-19
59MMCompleted24-jun-1824-jun-1824-jun-18NO24-feb-1924-feb-19
69MMCompleted24-jun-1825-jun-1826-jun-18YES14-feb-1926-jun-1926-jun-19
79MMCompleted24-jun-1825-jun-1826-jun-18NO14-feb-1926-jun-1926-jun-19
89MMCompleted24-jun-1825-jun-1826-jun-18YES26-apr-1926-apr-19
99MMCompleted24-jun-1825-jun-1826-jun-18NO26-feb-1926-feb-19
Sheet2
Cell Formulas
RangeFormula
I2:I9I2=IF(D2="","",MAX(EDATE(MAX(D2:F2),8+IF(G2="yes",2,0)),IF(H2="",0,MIN(EDATE(EDATE(MAX(D2:F2),8+((G2="yes")*2)),4),EDATE(MAX(D2:F2),12)))))

sorensjp

New Member
jorismoerings,

I think that will work. All appears VERY promising. I've been racking what (Little) Brains I have out on this.

I'll be testing this in my application I've been working on. But I'm very tired and need to work early. Please let me apply it in the Workbook tomorrow.
C_VILLE_WepsQual_LV TRKR(V1.25).xlsm
EFGHIJKLM
36Weapons Qualification Entry
37
38PQSNHQCHPWCHLLC2 Mo. ExtensionSustainmentExpiration DateCarry/No Carry
399 mm:8/4/20208/4/20208/4/2020YES2/7/202104-Aug-21
40RQCRLLC
41M16/M4:8/7/20208/7/2020YES1/7/202107-Jun-21
42SPWC
43Shotgun:8/7/2020YES
44COF
45M240:8/8/2020YES
46COF
47.50 Cal:8/8/2020YES
WEAPONS QUALS
Cell Formulas
RangeFormula
L39L39=IF(\$G\$39="","",MAX(EDATE(MAX(\$F\$39:\$I\$39),8+IF(\$J\$39="yes",2,0)),IF(\$K\$39="",0,MIN(EDATE(EDATE(MAX(\$F\$39:\$I\$39),8+((\$J\$39="yes")*2)),4),EDATE(MAX(\$F\$39:\$I\$39),12)))))
L41L41=IF(\$G\$41="","",MIN(MAX(EDATE(MAX(\$G\$41:\$H\$41),8+2*(\$J\$41="yes")),EDATE(\$K\$41,4)),EDATE(MAX(\$G\$41:\$H\$41),12)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F18:F20,F39,F41,F43,F45,F47Other TypeIcon setNO

This is part of the project now I need to apply your solution to the Table I'm using in the DATA sheet to to make sure it to runs the way I'm hoping.

Thank you very much for your assistance and solution.

V/r,
Jim

jorismoerings

Well-known Member
no problem - i hope it helps...

sorensjp

New Member
jorismoerings,

no problem - i hope it helps...

Okay, after a few attempts to convert your solution in a Table I finally conquered it. It does what I need. Thank you for your assistance.

V/r,
Jim

Replies
3
Views
425
Replies
5
Views
510
Replies
1
Views
372
Replies
21
Views
1K
Replies
3
Views
502

1,127,106
Messages
5,622,768
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back