Expiration Date Formula w/criteria

sorensjp

New Member
Joined
Jul 11, 2020
Messages
4
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
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)))
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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
Joined
Jul 11, 2020
Messages
4
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
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
 

sorensjp

New Member
Joined
Jul 11, 2020
Messages
4
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top