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

## Best Answer

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