This is where I got to and it is obviously wrong. I am really trying to have a singular output so I can do more with it. I put the screenshot below because I can’t figure out formatting on this forum.
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 84, "12 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 56, "8 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 28, "4 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TRUE({maintenance job}),
TODAY() >= {Due Date},
{Days Elapsed} >= 84, "2 weeks"
)
)
This is where I got to and it is obviously wrong. I am really trying to have a singular output so I can do more with it. I put the screenshot below because I can’t figure out formatting on this forum.
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 84, "12 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 56, "8 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date},
{Days Elapsed} >= 28, "4 weeks"
)
)
IF(
AND(
NOT({Work Completed}),
TRUE({maintenance job}),
TODAY() >= {Due Date},
{Days Elapsed} >= 84, "2 weeks"
)
)
All of your , "# weeks" bits are in the wrong spot and your IF() statements aren’t nested.
The , "# weeks" things should be outside your AND() statements and all but the last one should have a comma after them to begin nesting your IF()s. Move every singe IF()'s end parenthesis to the end of the whole formula.
A nested IF() looks like this:
IF(
first thing to test],
"value if first test is true",
IF(
second thing to test],
"value if second test is true",
"value if both test 1 and test 2 are false"
)
)
Thank you Kamille,
I got it!
IF(
AND(
NOT({Work Completed}),
TODAY() >= {Due Date}
),
IF(
{Days Elapsed} >= 84, "12 weeks",
IF(
{Days Elapsed} >= 56, "8 weeks",
IF(
{Days Elapsed} >= 28, "4 weeks",
IF(
{maintenance job} = 1,
IF(
{Days Elapsed} >= 14, "2 weeks"
)
)
)
)
)
)
