Help with IF() formula

Hey @Josh_Colina!

This might be a bit out of scope for exactly what you were looking for, but I’d rather over-deliver and then trim away from there to fully fit your needs.

So, I have a few example records here that will give you a full sense of how the formula behaves.
I added a couple of values so you can see the full array of what’s here.

IF(
    {Status} = "Done",
    "Complete!",
    IF(
        AND(
            {Status} = "In Progress",
            IS_AFTER(
                {Task Due Date},
                TODAY()
            )
        ),
        "On Schedule",
        IF(
            AND(
                OR(
                    {Status} = "Not Started",
                    {Status} = "In Progress"
                ),
                {Task Due Date} = TODAY()
            ),
            "Due Today!",
            IF(
                AND(
                    OR(
                        {Status} = "Not Started",
                        {Status} = "In Progress"
                    ),
                    IS_BEFORE(
                        {Task Due Date},
                        TODAY()
                    )
                ),
                "Delayed",
                IF(
                    AND(
                        {Status} = "Not Started",
                        {Task Due Date}
                    ),
                    "Not Started",
                    IF(
                        AND(
                            {Status} = "Not Started",
                            NOT(
                                {Task Due Date}
                            )
                        ),
                        "Missing Due Date!"
                    )
                )
            )
        )
    )
)

When you’re nesting a bunch of functions within one another, it can be really easy to lose track of the syntax if you aren’t used to keeping your structure tight.

When working through deeply nested formulas, I always like to either:

1. Write out, in plain language, the ‘story’ of what I’m trying to build. In some scenarios, I like to diagram it to keep track of what’s happening in my formula. The same thing is applicable when writing code.

2. Build the formula in modules. When thinking about the ‘story’, break it up into chapters and write it independently. Once you have all the parts of your modules, you can piece them together.

Just some things that help me when working through more complex formulas.

Hi Ben! Thanks a lot for your response! I actually want to limit only to the 2 returns (“Delayed” and “On Schedule”) because of the way I’d like to reference this field in another table. Your response has been really illuminating in how I sort of screwed up the syntax on the AND() and OR() commands. I think what I was doing was writing them sort of grammatically (IF this is true, AND this is true) instead of as a command on 2 statements.

I’ve reconfigured my formula below, but this still doesn’t read. Do you have any feedback on where I’m going wrong here?

IF(
  OR(
    AND(
      IS_BEFORE({Task Start Date}, TODAY())
      Status = 'Not Started'
      )
    AND(
      Status != 'Done'
      OR(
        IS_SAME({Task Due}, TODAY()
        )
        IS_BEFORE({Task Due}, TODAY()
        )
      )
    )
  ), "Delayed", "On Schedule"
)

Hello!

It would seem that your syntax errors are coming from missing commas between some of your formula’s statements.

I’ll highlight where you’re missing them:

1IF(
2    AND(
3        {Status} = "Not Started",
4        IS_BEFORE(
5            {Task Start Date},
6            TODAY()
7        )
8    ),
9    "Delayed",
10    IF(
11        AND(
12            OR(
13                {Status} = "Not Started"❌
14                {Status} = "In Progress"
15            ),
16            IS_BEFORE(
17                {Task Due},
18                TODAY()
19            )
20        ),
21        "Delayed"❌
22        IF(
23            AND(
24                OR(
25                    {Status} = "Not Started"❌
26                    {Status} = "In Progress"
27                ),
28                IS_SAME(
29                    {Task Due},
30                    TODAY()
31                )
32            ),
33            "Delayed"
34        )
35    )
36)

Here’s the corrected syntax version:

IF(
    AND(
        {Status} = "Not Started",
        IS_BEFORE(
            {Task Start Date},
            TODAY()
        )
    ),
    "Delayed",
    IF(
        AND(
            OR(
                {Status} = "Not Started",
                {Status} = "In Progress"
            ),
            IS_BEFORE(
                {Task Due},
                TODAY()
            )
        ),
        "Delayed",
        IF(
            AND(
                OR(
                    {Status} = "Not Started",
                    {Status} = "In Progress"
                ),
                IS_SAME(
                    {Task Due},
                    TODAY()
                )
            ),
            "Delayed"
        )
    )
)

Lemme know if you keep getting errors!
I apologize for being kinda sloppy, I was jumping around tasks and didn’t get to properly consolidate my efforts through the day.