Skip to main content
Solved

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

  • October 26, 2020
  • 3 replies
  • 37 views
A

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

Best answer by Anthony_Tonkin

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

A
  • Anthony_Tonkin
  • Author
  • New Participant
  • October 27, 2020

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

Kamille_Parks11
Forum|alt.badge.img+27

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"
   )
)
A
A
  • Anthony_Tonkin
  • Author
  • New Participant
  • Answer
  • October 28, 2020

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"
				    ) 	
				)
            )
        )
    ) 
)
Kamille_Parks11
Terms & ConditionsAccessibility statement