IFS(OR) statement returning #VALUE error for referenced cells that are blank

Question

Hello,

I'm trying to get column E to populate a value based on these conditions:

If L20 displays "Position" OR "Phase I", it should display "Exempt"

If H20 has a date that has not yet passed, it should display "Certified"

If H20 has no value OR H20 has a date that has already passed, it should display "Not Certified"

I've written out the formula two different ways:

=IFS(OR(L20="POSITION",L20="PHASE I"),"EXEMPT",DATE(YEAR(H20),MONTH(H20),DAY(H20))>=TODAY(),"Certified",OR(ISBLANK(H20),DATE(YEAR(H20),MONTH(H20),DAY(H20)),"Not Certified")

=IFS(OR(L20="POSITION",L20="PHASE I"),"EXEMPT",DATE(YEAR(H20),MONTH(H20),DAY(H20))>=TODAY(),"Certified",OR(H20="",DATE(YEAR(H20),MONTH(H20),DAY(H20)),"Not Certified")

I've also tried putting the OR statement containing the ISBLANK(H20) reference first. That produced the same result.

The problem I'm encountering is that both formulas are displaying cells that (based on the criteria: H20 is blank) should be "Not Certified" as "#VALUE"

Anyone have a solution to this?

Answer 1

=IFS(OR(L20={"Position","Phase 1"}), "Exempt", H20>TODAY(), "Certified", TRUE, "Not Certified")