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

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")`

## 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")`

`=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))),)
``````
• condition1: if P6=never opened, then blank OR condition2: if P6=closed AND Q6<today, then Q6-B6, otherwise TODAY() -B6. the `IFERROR` helped hide any errors Aug 17, 2018 at 18:25
• i slightly modified my original conditions (see comment above). my current formula: `=ArrayFormula(IFERROR(IF(P6="never opened", "", IF(AND(P6="closed", Q6<"Today()"), Q6-B6, TODAY()-B6))))` it works great (no negative values), but if B6 is empty, then R6 ends up being a string of numbers that changes every day. today it's `43329`, yesterday =`43328`, 2 days ago = `43327`, etc. how do i get this go way using my current formula (w/ updated conditions)? i thought it might be due to the cell formatting, but i changed col R to automatic, plain text, and number format and the `43328` still shows Aug 17, 2018 at 18:31
• 43329, 43328, 43327 etc. are dates formatted as numbers. I would need to see it to tell... Aug 17, 2018 at 18:45
• no, the output of the formula (column R) is a number value (columns Q and B are dates). the formula tells me the time (# days) since opening (col B) and closing (col Q) Aug 17, 2018 at 19:05
• thanks, @user0! the new solution `=IFERROR(IF(P6="never opened", "", IF(B6="", "", IF(AND(P6="closed", Q6<"NOW()"), Q6-B6, TODAY()-B6))),)` works wonderfully :) Aug 17, 2018 at 20:00