IF/And OR vs calculating DOB less than 18 years of Age

[nr6281]

=IF(AL2="",AND(OR(S2="IN",S2="KY",S2="HI",S2="WV")*TODAY()-N2/365))

Above is the formula I wrote to calculate less than 18 years for particular states.

This is what I a trying to achieve and not sure how to go about it.

Column AL has ID
Column S has States
Column N has DOB
Column AE has Enroll Date

Now if Column AL = Blank and Column S has the states listed above i:e IN, KY, HI, TN, WV and the DOB (Age of the student is below 18) from the Date of Enroll in Column AE is should highlight as TRUE

Can this be done?

Thank you in Advance

https://www.excelforum.com/excel-fo...alculating-dob-less-than-18-years-of-age.html

Here is a template attached.

 

[jorismoerings]

Hi,

Similar to my answer on Excelforum, try this:

=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"KY";"IN";"HI";"WV"},0))),(DATEDIF(D2,E2,"y")<18))

 

[nr6281]

Hi This works like gem, thank you so much.

Could you explain how this works I am not able to quite figure out coz i have another file with similar thing but instead of the date to date calculation. The formula should pick a date and say if its over 7 years for particular states and if it has key words such as release date in another column

 

[jorismoerings]

Hi,

the formula uses a few excel functions:
=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"KY";"IN";"HI";"WV"},0))),(DATEDIF(D2,E2,"y")<18))

1. AND function checks if all entered statements are true, if so, it will return TRUE.
2. ISBLANK - checks if a cell is blank and returns TRUE or FALSE.
3. the state check uses a few functions combined:
   1. MATCH(C2,{"KY";"IN";"HI";"WV"},0)) checks if the value in C2 is part of the list mentioned between {} if they match it will return the place in the list, if it doesn't part of the list, it will return an error.
   2. ISNA () check if the statement returns an error
   3. NOT() reverses the statement
   4. so if the state is in the list (eg "IN") the MATCH part will return 2 as the place in the list.
   5. because the match returns 2 the ISNA part will return FALSE
   6. the NOT function will revert the FALSE into TRUE.
4. DATEDIF calculates the number of years between the 2 mentioned dates cobined with "<18" checks if the result is lower than 18 years and return true or false depending the outcome.

Am not sure if this helps you with the other problem. If not, create a new forum thread and i will try to help you.

 

[nr6281]

HI thank you for clear explanation on this I have written somewhat the same formula for the other file but I was not able to do so. I have created another forum for that question since I am unable to attach the template here. here is the link https://www.excelforum.com/excel-fo...er-7-years-from-current-date.html#post5172230