Age eligibility

[apgmin]

In cell M2 is mentioned the date of birth of the student.
In cell Q2 is mentioned the course he or she is applying for ( It is a drop down list consisting of "MBA" and 3 other entries, IMBA, BBA, BHA )
In cell Y15 is mentioned todays date ( volatile )

Now If Q2 is selected as MBA
then the age should be minimum 21 years as on today

If Q2 is selected as BBA or BHA the minimum age should be 18 years as on today

Now If Q2 is selected as IMBA, then the age should be minimum 20 years as on today

Now if the age is less than the above, the message in N2 should be "not eligible" or else it should mention "eligible"

Please help

 

[oldbrewer]

=if(q2="MBA", if(Y15-m2<21,"not eligible","eligible), if(q2=

build it up from there

 

[Cyberpunk001]

Hi apgmin,

I created a quick sheet with your columns you referenced, and typed this into column z, this will output if eligible or not:

IF(AND(OR(Q5="BBA",Q5="BHA"),DAYS(Y5,M5)/365>18),"ELIGIBLE",IF(AND(Q5="MBA",DAYS(Y5,M5)/365>21),"ELIGIBLE",IF(AND(Q5="IMBA",DAYS(Y5,M5)/365>20),"ELIGIBLE","NOT ELIGIBLE")))

Hope this works for your needs

 

[Cyberpunk001]

Hi apgmin,

You can also replace the values in the formula with the cell references you posted. Should work fine, just remember your absolute referencing.

 

[oldbrewer]

COL N

MYTABLE

COURSE

DOB

DECISION

ALAN

IMBA

01/06/1995

ELIGIBLE

ROW 20

BBA

6574.5

27/08/2018

BILL

BBA

06/11/1995

ELIGIBLE

BHA

6574.5

DAVE

MBA

12/04/1996

ELIGIBLE

IMBA

7305

ED

BHA

17/09/1996

ELIGIBLE

MBA

7670.25

FRED

IMBA

22/02/1997

ELIGIBLE

HARRY

BBA

30/07/1997

ELIGIBLE

IAN

MBA

04/01/1998

INELIGIBLE

JAMES

BHA

11/06/1998

ELIGIBLE

KEITH

IMBA

16/11/1998

INELIGIBLE

LARRY

BBA

23/04/1999

ELIGIBLE

MIKE

MBA

28/09/1999

INELIGIBLE

FORMULA FOR ALAN DECISION

NICK

BHA

04/03/2000

ELIGIBLE

OSCAR

IMBA

09/08/2000

INELIGIBLE

=IF($N$20-D20<VLOOKUP(C20,MYTABLE,2),"INELIGIBLE","ELIGIBLE")

PETE

BBA

14/01/2001

INELIGIBLE

QUENTIN

MBA

21/06/2001

INELIGIBLE

ROBERT

BHA

26/11/2001

INELIGIBLE

SID

IMBA

03/05/2002

INELIGIBLE

TOM

BBA

08/10/2002

INELIGIBLE

WALTER

MBA

15/03/2003

INELIGIBLE

<colgroup><col span="3"><col><col><col span="8"><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

=IF($N$20-D20 < VLOOKUP(C20,MYTABLE,2),"INELIGIBLE","ELIGIBLE")

<tbody>
</tbody>