Can't get correct Format for IF(ISBLANK) and AND (&&&) for complex formula IF AND Calculation

[LeonardoOt]

Hi there

I'm trying to create a somewhat Complex calculated expression for 6 Separate data fields where the DATE is populated or empty.

I was able to get TWO arguments in the expression to give correct results, but run into trouble when the IF and the && come in.

I don't think I can use the OR || because it's not an OR type of expression.

It would go like this, but it has all be combined

Basically in human language first:

If my first field has no date, then "Start Checklist". If My First field is Populated and the Other 5 are blank then 'I have completed First Check". If My first and second fields have a date then "I have completed Checks 1 and 2".....and so on.

IF(ISBLANK({field A},"Start Checklist",,IF(!ISBLANK({field A})&&ISBLANK({field 2},{field 3},{field 4},{field 5},{field 6}),"I have completed First check",IF(!ISBLANK({field A},{field 2})&&ISBLANK({field 3},{field 4},{field 5},{field 6}),"I have completed check 1 and 2"....and so on.

Is this possible?

Thanks

[skyehansen]

I'd start with this link for your syntax.

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

For example the ISBLANK syntax is "ISBLANK(value)" -- it's not "ISBLANK(value, value2, value3, and so on)"

Because I hate building these types of chains (it's so menial...), I also recommend going through AI (e.g. copilot) to help automate an answer. I'm not saying the below is necessarily accurate, but it's a start at showing you potentially what syntax you should be looking at. AI even helpfully throws in an "in Progress" answer in case some smart aleck starts filling out the fields backwards.

Remember to put in all your "DE:"s in front of your field names.

IF(ISBLANK({Field A}),

  "Start Checklist",

  IF(!ISBLANK({Field A}) && ISBLANK({Field B}) && ISBLANK({Field C}) && ISBLANK({Field D}) && ISBLANK({Field E}) && ISBLANK({Field F}),

    "I have completed First check",

    IF(!ISBLANK({Field A}) && !ISBLANK({Field B}) && ISBLANK({Field C}) && ISBLANK({Field D}) && ISBLANK({Field E}) && ISBLANK({Field F}),

      "I have completed check 1 and 2",

      IF(!ISBLANK({Field A}) && !ISBLANK({Field B}) && !ISBLANK({Field C}) && ISBLANK({Field D}) && ISBLANK({Field E}) && ISBLANK({Field F}),

        "I have completed check 1 to 3",

        IF(!ISBLANK({Field A}) && !ISBLANK({Field B}) && !ISBLANK({Field C}) && !ISBLANK({Field D}) && ISBLANK({Field E}) && ISBLANK({Field F}),

          "I have completed check 1 to 4",

          IF(!ISBLANK({Field A}) && !ISBLANK({Field B}) && !ISBLANK({Field C}) && !ISBLANK({Field D}) && !ISBLANK({Field E}) && ISBLANK({Field F}),

            "I have completed check 1 to 5",

            IF(!ISBLANK({Field A}) && !ISBLANK({Field B}) && !ISBLANK({Field C}) && !ISBLANK({Field D}) && !ISBLANK({Field E}) && !ISBLANK({Field F}),

              "All checks completed",

              "In Progress"

            )

          )

        )

      )

    )

  )

)

By the way, the best way to get an accurate answer out of AI is to show it something that works. So even if you can get something small working, like field A is not blank and field B is blank -- AI can more successfully extrapolate up to field F for you.

[LeonardoOt]

Thank you so much @skyehansen! This worked!!!!  Much appreciated on the help and the additional Syntax info. 

Have a great day!