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

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

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

Screen Shot 2020-10-27 at 8.46.51 pm

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

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.