Accepted Solutions
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.
