Nested if formula

[Tanya_Payne]

Hi all,

I am struggling to find the error in my nested if() statement. I am trying to create a ‘status’ field for project outputs based on their due dates, using a check box ‘complete’ field, and two date fields.

If checkbox complete is checked, show “Complete”
If not complete and the original due date has not passed, show “On Track”
If not complete but original due date has passed but not the tolerance (adjusted) due date has not passed, show “Overdue within tolerance”
If not complete, but both original and tolerance (two seperate date fields) have passed, show “Overdue exceeding tolerance”

Currently the formula I’ve used is this:
IF({Complete?} = ‘1’, “Complete”,IF(IS_BEFORE({Baseline due date (DD,MM,YYYY)}, TODAY()), “Overdue within tolerance”, IF(IS_AFTER({Tolerance (no Covid)}, TODAY(), “Overdue exceeding tolerance”, “On Track”)))).

I am not sure if I should be using an AND somewhere. Currently the formula is accepted but only ‘Complete’ and ‘Overdue within tolerance’ are returned.

Any help would be much appreciated!

[Zollie]

Two things that might help you handle this kind of issue in the future (1) test your code in smaller chunks (2) indent your code so you can more easily spot syntax errors.

Here’s your code indented but still buggy.

IF(
    {Complete?} = ‘1’,
    “Complete”,
    IF(
        IS_BEFORE(
            {Baseline due date (DD,MM,YYYY)},
            TODAY()
        ),
        “Overdue within tolerance”,
        IF(
            IS_AFTER(
                {Tolerance (no Covid)},
                TODAY(),
            “Overdue exceeding tolerance”,
            “On Track”
        )
    )
))

There might be other errors, but on first glance I can see you have an extra parenthesis at the end, and IS_AFTER is missing a closing parenthesis.

[kuovonne]

You need to change the logic of the formula. I also recommend using the same comparison operator (IS_AFTER vs IS_BEFORE) and put the dates to be compared in matching order.

You can also simplify the initial check on the {Complete?} field.

IF(
  {Complete?},
  "Complete",
  IF(
    IS_AFTER({Tolerance (no Covid)},TODAY()),
    "Overdue exceeding tolerance",
    IF(
      IS_AFTER({Baseline due date (DD,MM,YYYY)}, TODAY()),
      "Overdue within tolerance",
      "On Track"
    )
  )
)