1

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

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

1

=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 – que syrah sarah Aug 17 '18 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 – que syrah sarah Aug 17 '18 at 18:31
  • 43329, 43328, 43327 etc. are dates formatted as numbers. I would need to see it to tell... – user0 Aug 17 '18 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) – que syrah sarah Aug 17 '18 at 19:05
  • 1
    thanks, @user0! the new solution =IFERROR(IF(P6="never opened", "", IF(B6="", "", IF(AND(P6="closed", Q6<"NOW()"), Q6-B6, TODAY()-B6))),) works wonderfully :) – que syrah sarah Aug 17 '18 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.