Expiration Date Formula w/criteria

[sorensjp]

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
	B	C	D	E	F	G	H	I	J	K
2		PQS	NHQC	HPWC	HLLC	2 Month EXTENSION	SUSTAINMENT	QUALIFICATION EXPIRATION	Correct Expiration Date
3	9MM	Completed	24-Jun-18	24-Jun-18	24-Jun-18	YES	14-Feb-19	14-Jun-19	24-Jun-19
4	9MM	Completed	24-Jun-18	24-Jun-18	24-Jun-18	NO	14-Feb-19	14-Jun-19	24-Jun-19
5	9MM	Completed	24-Jun-18	24-Jun-18	24-Jun-18	YES		24-Apr-19	24-Apr-19
6	9MM	Completed	24-Jun-18	24-Jun-18	24-Jun-18	NO		24-Feb-19	24-Feb-19
7	9MM	Completed	24-Jun-18	25-Jun-18	26-Jun-18	YES	14-Feb-19	14-Jun-19	26-Jun-19
8	9MM	Completed	24-Jun-18	25-Jun-18	26-Jun-18	NO	14-Feb-19	14-Jun-19	26-Jun-19
9	9MM	Completed	24-Jun-18	25-Jun-18	26-Jun-18	YES		26-Apr-19	26-Apr-19
10	9MM	Completed	24-Jun-18	25-Jun-18	26-Jun-18	NO		26-Feb-19	26-Feb-19
11
12
13
14					Test
15		NHQC	HPWC	HLLC	2 Month (HELPER Cell)	2 Month EXTENSION	SUSTAINMENT (HELPER Cell)	SUSTAINMENT	QUALIFICATION EXPIRATION	Correct Expiration Date
16	9MM	24-Feb-19	24-Feb-19	24-Feb-19	1	YES	Formula Here?		24-Oct-19	24-Oct-19
17	9MM	24-Feb-19	24-Feb-19	24-Feb-19	0	NO	Formula Here?		24-Oct-19	24-Oct-19
Sheet1 (2)

Cell Formulas
Range	Formula
I3:I10	I3	=IF(D3="","",MIN(MAX(EDATE(MAX(D3:F3),8+2*(G3="yes")),EDATE(H3,4)),EDATE(MAX(D3:F3),12)))
F16:F17	F16	=IF(G16="YES",1,"0")
J16	J16	=IF(C16="","0",MIN(MAX(EDATE(MAX(C16:E16),8+2*(F16="1")),EDATE(I16,4)),EDATE(MAX(D16:F16),12)))
J17	J17	=IF(C17="","0",MIN(MAX(EDATE(MAX(C17:E17),8+2*(G17="1")),EDATE(I17,4)),EDATE(MAX(D17:F17),12)))

 

[jorismoerings]

Hi,

Try this:

Book1
	B	C	D	E	F	G	H	I	J
1		PQS	NHQC	HPWC	HLLC	2 Month EXTENSION	SUSTAINMENT	QUALIFICATION EXPIRATION	Correct Expiration Date
2	9MM	Completed	24-jun-18	24-jun-18	24-jun-18	YES	14-feb-19	24-jun-19	24-jun-19
3	9MM	Completed	24-jun-18	24-jun-18	24-jun-18	NO	14-feb-19	24-jun-19	24-jun-19
4	9MM	Completed	24-jun-18	24-jun-18	24-jun-18	YES		24-apr-19	24-apr-19
5	9MM	Completed	24-jun-18	24-jun-18	24-jun-18	NO		24-feb-19	24-feb-19
6	9MM	Completed	24-jun-18	25-jun-18	26-jun-18	YES	14-feb-19	26-jun-19	26-jun-19
7	9MM	Completed	24-jun-18	25-jun-18	26-jun-18	NO	14-feb-19	26-jun-19	26-jun-19
8	9MM	Completed	24-jun-18	25-jun-18	26-jun-18	YES		26-apr-19	26-apr-19
9	9MM	Completed	24-jun-18	25-jun-18	26-jun-18	NO		26-feb-19	26-feb-19
Sheet2

Cell Formulas
Range	Formula
I2:I9	I2	=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]

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
	E	F	G	H	I	J	K	L	M
36				Weapons Qualification Entry
37
38		PQS	NHQC	HPWC	HLLC	2 Mo. Extension	Sustainment	Expiration Date	Carry/No Carry
39	9 mm:		8/4/2020	8/4/2020	8/4/2020	YES	2/7/2021	04-Aug-21
40			RQC	RLLC
41	M16/M4:		8/7/2020	8/7/2020		YES	1/7/2021	07-Jun-21
42			SPWC
43	Shotgun:		8/7/2020			YES
44			COF
45	M240:		8/8/2020			YES
46			COF
47	.50 Cal:		8/8/2020			YES
WEAPONS QUALS

Cell Formulas
Range	Formula
L39	L39	=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)))))
L41	L41	=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
Cell	Condition		Cell Format	Stop If True
F18:F20,F39,F41,F43,F45,F47	Other Type	Icon set		NO

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]

no problem - i hope it helps...

 

[sorensjp]

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