Multiple IF statements: IF, AND, ISDATE, THEN formula

Question

I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had another reviewer write a code for me to conditionally format a cell with similar criteria and it worked so I figured I could use it for this situation. Unfortunately, it doesn't work. Can someone kindly help me understand what I did wrong?

If both these conditions are met:

• P6 = "closed"

AND

• Q6 = date

Then:

• Q6-P6 (subtract)
• otherwise, leave blank

EDIT: added this second condition: any errors or negative values that result become "NA". I think it will be some sort of nested statement?

IF: negative number or error (#VALUE!)

THEN: "NA"

---

Formulas I tried:

=IF( AND(ISDATE(Q6), COUNTIFS(P6, "closed", Q6, ">"&DATE(0,1,1)), "Q6-B6", ""))

AND

=IF(AND(P6="closed", Q6">"&DATE( 0,1,1), Q6-B6, ""))

AND

=IF(AND(P6= “closed”, ISDATE(Q6 ">"&DATE(0,1,1))), Q6-B6, "")

---

Solution #1

I figured it out! Was a simple fix; just set the second condition as any date less than today.

=IF(AND(P6="closed", Q6<"Today()"), Q6-B6, "")

BUT now if I get a negative number (because column B doesn't have a start date), how can I make this formula return "NA?"

I tried this formula (got an error):

=IF(AND(P6="closed", Q6<"Today()", Q6-B6, ""), IF(R6<0, "NA", IF(R6="VALUE!", "NA", "NA")))

Am I on the right track? Does this fail because if it errors/is negative is circular (it's the result of the first part of the formula running)? I got the negative value to disappear using conditional formatting and making the text font white (thank you, @user0!)

Should the error piece be written as an array/error trap? the IFERROR code completely confuses me. I tried these but still got Formula parse error:

=iferror(ArrayFormula(IF(AND(P6="closed")*(Q6<"Today()"),Q6-B6), "NA”))

=iferror(ArrayFormula(IF(AND(P6="closed")*( Q6<"Today()"),Q6-B6, “”),1)

=iferror(ArrayFormula(IF(AND(P6="closed")*( Q6<"Today()"),Q6-B6, “”),1,"NA")

Solution #2

Wow. I feel pretty accomplished. Lots of trial and error, but this formula seems to work for all cases. Gives me the count Q6-B6, NA if there's an error, and blank if conditions aren't met (the negative values I used conditional formatting to hide).

=IFERROR(IF(AND(P6="closed", Q6<"Today()"), Q6-B6, ""), "NA")

Answer 1

Solution #1

I think I figured it out! I just set the second condition to be a date less than today (since the dates in column Q will be the closure date, they are dates in the past). I modified the generic IF-AND formula:

=IF(AND(P6="closed", Q6<"Today()"), Q6-B6, "")

Solution #2

For the negative values, I used conditional formatting and made the text white (credit goes to @user0, who helped me with another issue and used this ingenious trick)

For the errors, I used an error trap formula (also thanks to @user0). Took a little tweaking and a lot of trial and error, but I finally got it to work!! :)

=IFERROR(IF(AND(P6="closed", Q6<"Today()"), Q6-B6, ""), "NA")

Answer 2

=IFERROR(IF(AND(P6="closed", Q6<"Today()"), Q6-B6, ""), "NA")

can be slightly improved to:

=IFERROR(
         IF(
            AND(P6 = "closed"; 
                Q6 < "NOW()"); 
                               IF((Q6-B6) < 0; 
                                               ""; 
                                               Q6-B6); 
                               ""); 
         "NA")

• where NOW() is for more accuracy (if needed), because it can include the already passed time of today's day.
• and 2nd IF() is able to detect and capture negative values, so there is no need for conditional formatting

---

final solution for OP:

=IFERROR(IF(P6="never opened", "",
         IF(B6="", "", 
         IF(AND(P6="closed", Q6<"NOW()"), Q6-B6, TODAY()-B6))),)