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"
- Q6 = date
- 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!)
Formulas I tried:
=IF( AND(ISDATE(Q6), COUNTIFS(P6, "closed", Q6, ">"&DATE(0,1,1)), "Q6-B6", ""))
=IF(AND(P6="closed", Q6">"&DATE( 0,1,1), Q6-B6, ""))
=IF(AND(P6= “closed”, ISDATE(Q6 ">"&DATE(0,1,1))), Q6-B6, "")
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, “”),1)
=iferror(ArrayFormula(IF(AND(P6="closed")*( Q6<"Today()"),Q6-B6, “”),1,"NA")
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")