# Help with IF() formula

Topic Labels: Base design
Solved
1830 5
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hi there! Still learning how to make full use of formulas and write more useful IF() statements.

In this case, I’d like to flag tasks that should have been started but haven’t been or that haven’t been marked finished after their deadline is either today or has already passed as “Delayed” (and otherwise are “On Schedule”)

Of note is that my Status field is a single-select field, so not sure if that “=” is what’s throwing this off or if it’s just fundamentally way off-base. Any help is appreciated!

``````IF(
AND(
Status = 'Not Started')
OR(
OR(
AND(
Status != 'Done'
)
)
)
,"Delayed", "On Schedule"
)
``````
1 Solution

Accepted Solutions
11 - Venus

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(
6            TODAY()
7        )
8    ),
9    "Delayed",
10    IF(
11        AND(
12            OR(
13                {Status} = "Not Started"❌
14                {Status} = "In Progress"
15            ),
16            IS_BEFORE(
18                TODAY()
19            )
20        ),
21        "Delayed"❌
22        IF(
23            AND(
24                OR(
25                    {Status} = "Not Started"❌
26                    {Status} = "In Progress"
27                ),
28                IS_SAME(
30                    TODAY()
31                )
32            ),
33            "Delayed"
34        )
35    )
36)
``````

Here’s the corrected syntax version:

``````IF(
AND(
{Status} = "Not Started",
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
IS_SAME(
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.

5 Replies 5
11 - Venus

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(
TODAY()
)
),
"On Schedule",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
),
"Due Today!",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
{Status} = "Not Started",
),
"Not Started",
IF(
AND(
{Status} = "Not Started",
NOT(
)
),
"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.

6 - Interface Innovator

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(
Status = 'Not Started'
)
AND(
Status != 'Done'
OR(
)
)
)
)
), "Delayed", "On Schedule"
)
``````
6 - Interface Innovator

EDIT: I tried the formula again but as a nested IF() statement like you demonstrated, but still won’t read. I keep going through the syntax and it makes sense to me, so I’m unsure where I’m going off-roads here?

``````IF(
AND(
{Status} = "Not Started",
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
OR(
{Status} = "Not Started"
{Status} = "In Progress"
),
IS_BEFORE(
TODAY()
)
),
"Delayed"
IF(
AND(
OR(
{Status} = "Not Started"
{Status} = "In Progress"
),
IS_SAME(
TODAY()
)
),
"Delayed"
)
)
)
``````
11 - Venus

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(
6            TODAY()
7        )
8    ),
9    "Delayed",
10    IF(
11        AND(
12            OR(
13                {Status} = "Not Started"❌
14                {Status} = "In Progress"
15            ),
16            IS_BEFORE(
18                TODAY()
19            )
20        ),
21        "Delayed"❌
22        IF(
23            AND(
24                OR(
25                    {Status} = "Not Started"❌
26                    {Status} = "In Progress"
27                ),
28                IS_SAME(
30                    TODAY()
31                )
32            ),
33            "Delayed"
34        )
35    )
36)
``````

Here’s the corrected syntax version:

``````IF(
AND(
{Status} = "Not Started",
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
IS_BEFORE(
TODAY()
)
),
"Delayed",
IF(
AND(
OR(
{Status} = "Not Started",
{Status} = "In Progress"
),
IS_SAME(
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.

6 - Interface Innovator

Thank you so much! A little embarrassing haha, but good to know for the future!