Jul 28, 2022 03:03 PM
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(
IS_BEFORE({Task Start Date}, TODAY())
AND(
Status = 'Not Started')
OR(
IS_BEFORE({Task Due}, TODAY())
OR(
IS_SAME({Task Due}, TODAY())
AND(
Status != 'Done'
)
)
)
,"Delayed", "On Schedule"
)
Solved! Go to Solution.
Jul 28, 2022 07:40 PM
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.
Jul 28, 2022 04:42 PM
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:
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.
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.
Jul 28, 2022 05:18 PM
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"
)
Jul 28, 2022 06:48 PM
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(
{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"
)
)
)
Jul 28, 2022 07:40 PM
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.
Jul 30, 2022 12:17 PM
Thank you so much! A little embarrassing haha, but good to know for the future!