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

#### nr6281

##### New Member
=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?

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

Here is a template attached.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### jorismoerings

##### Well-known Member
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

#### nr6281

##### New Member
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

##### Well-known Member
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.