Help

Formula to track days past a set date, multiple outputs

Topic Labels: Formulas
Solved
Jump to Solution
1449 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Anthony_Tonkin
5 - Automation Enthusiast
5 - Automation Enthusiast

I keep trying to google, search and i am not landing on what i need. My coding understanding, as minimal as it is, is only enough to get me into trouble.

As a general expression of what i wish to achieve; i want to make a view, that shows items::

  • are over 28 days or more beyond creation
  • without being completed.
  • that don’t have a specific due date that has passed (If it has passed due date then report as normal from created date).

I need a formula that:
Looks at the field “Created Date” field and compares to todays date.
Looks at the Field “Work Completed” (If checked or not) returning “nil”? if it is complete.
Looks at the field “Due Date” field and if (“today”) is not past that date, returns “nil”.

Outputs “4 weeks+” for any period 28-55 days
Outputs “8 weeks+” for any period 56-83 days
Outputs "12 weeks+ for any period 84 days

Thanks in advance!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Anthony_Tonkin! :grinning_face_with_big_eyes: I recommend doing this with two formulas: one to calculate the day difference between TODAY() and {Created Date}, and the other with the rest of your logic. That way you only calculate that difference once; otherwise you’ll end up repeating the same calculation several times, which IMO is unnecessary.

The date comparison field (which I’m calling {Days Elapsed}), use this formula:

DATETIME_DIFF(TODAY(), {Created Date}, "days")

The other formula would look like this:

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

Screen Shot 2020-10-09 at 7.18.38 PM

The {Days Elapsed} field can be hidden, of course, but I left it visible to show the values.

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Anthony_Tonkin! :grinning_face_with_big_eyes: I recommend doing this with two formulas: one to calculate the day difference between TODAY() and {Created Date}, and the other with the rest of your logic. That way you only calculate that difference once; otherwise you’ll end up repeating the same calculation several times, which IMO is unnecessary.

The date comparison field (which I’m calling {Days Elapsed}), use this formula:

DATETIME_DIFF(TODAY(), {Created Date}, "days")

The other formula would look like this:

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

Screen Shot 2020-10-09 at 7.18.38 PM

The {Days Elapsed} field can be hidden, of course, but I left it visible to show the values.

Thank you kindly. Very helpful indeed.