Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Formula to track days past a set date, multiple outputs (pt2)

Topic Labels: Formulas
Solved
313 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I am looking at attempting to combine the two functions into a single input. The second is to catch jobs that go overdue earlier.

Original Question: Formula to track days past a set date, multiple outputs

Justin Barret provided this code:
IF( AND( NOT({Work Completed}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 84, "12", IF( {Days Elapsed} >= 56, "8", IF( {Days Elapsed} >= 28, "4" ) ) ) & " weeks+" )

and I was able to use that to create this:

IF( AND( TRUE({maintenance job}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 14, "2 weeks+" ) ) )

1 Solution

Accepted Solutions
5 - Automation Enthusiast

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"
)
)
)
)
)
)
3 Replies 3
5 - Automation Enthusiast

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"
)
)

16 - Uranus

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"
)
)
5 - Automation Enthusiast

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"
)
)
)
)
)
)