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

nr6281

New Member
Joined
Jun 19, 2019
Messages
37
=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.
 

Some videos you may like

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
Joined
Jul 4, 2014
Messages
1,263
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

New Member
Joined
Jun 19, 2019
Messages
37
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
Joined
Jul 4, 2014
Messages
1,263
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,020
Messages
5,466,089
Members
406,464
Latest member
buks1232000

This Week's Hot Topics

Top