Google Sheets – Multiple IF Statements with ISDATE and THEN Formula

formulasgoogle sheetsgoogle-sheets-custom-functionworksheet-function

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

Related Topic