I'm trying to run an array formula to see how long (days) it is in a current status.

Source:

={
    "Days since application submitted";
    ArrayFormula(
        If(
            Isblank(U3:U),
            "",
            (
                IF(
                    G3:G="APPLICATION SENT",
                    DATEDIF(U3:U,AA3:AA,"D"),
                    (
                        IF(
                            G3:G="APPLICATION ACCEPTED - AWAITING REPAYMENT",
                            DATEDIF(U3:U,AF3:AF,"D"),
                            (
                                IF(
                                    G3:G="CREDIT",
                                    DATEDIF(U3:U,AF3:AF,"D"),
                                    (
                                        IF(
                                            G3:G="COMPLETED",
                                            DATEDIF(U3:U,AF3:AF,"D"),
                                        
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
}

It works fine however if I don't have anything in column 'AF' as I may skip a status it will show up with an error. But if 'AF' is blank I then want to do another check within the same array formula to run something like (IF(ISBLANK(AF3:AF),"",(IF(G3:G="COMPLETED",DATEDIF(U3:U,AJ3:AJ,"D") ... Looking at another date in 'AJ' column.

={
    "Days since application submitted";
    ArrayFormula(
        If(
            Isblank(U3:U),
            "",
            (
                IF(
                    G3:G="APPLICATION SENT",
                    DATEDIF(U3:U,AA3:AA,"D"),
                    (
                        IF(
                            G3:G="APPLICATION ACCEPTED - AWAITING REPAYMENT",
                            DATEDIF(U3:U,AF3:AF,"D"),
                            (   
                                IF(
                                    G3:G="CREDIT",
                                    DATEDIF(U3:U,AF3:AF,"D"),
                                    (
                                        IF(
                                            G3:G="COMPLETED",
                                            DATEDIF(U3:U,AF3:AF,"D"),
                                            OR(
                                                if(
                                                    isblank(
                                                        AF3:AF,
                                                        IF(
                                                            G3:G="COMPLETED",
                                                            DATEDIF(U3:U,AJ3:AJ,"D"),
                                                            
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
}

Any support would be helpful where I am going wrong.

CodePudding user response：

Instead of nesting many if() functions, try ifs(), like this:

=arrayformula( 
  { 
    "Days since application submitted"; 
    ifs( 
      isblank(U3:U), 
        iferror(1/0), 
      isblank(AF3:AF) * (G3:G = "COMPLETED"), 
        datedif(U3:U, AJ3:AJ, "D"), 
      G3:G = "APPLICATION SENT", 
        datedif(U3:U, AA3:AA, "D"), 
      regexmatch(to_text(G3:G), "(?i)accepted|credit|completed"), 
        datedif(U3:U, AF3:AF, "D"), 
      true, 
        na() 
    ) 
  } 
)

CodePudding user response：

try:

={"Days since application submitted"; ARRAYFORMULA(
 IF(ISBLANK(U3:U),,
 IF(G3:G="APPLICATION SENT", DATEDIF(U3:U, AA3:AA, "D"),
 IF(ISBLANK(AF3:AF),,
 IF(REGEXMATCH(""&G3:G, "APPLICATION ACCEPTED - AWAITING REPAYMENT|CREDIT|COMPLETED"), 
    DATEDIF(U3:U, AF3:AF, "D"))))))}