Formula to track days past a set date, multiple outputs

[Anthony_Tonkin]

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!

[Justin_Barrett · Accepted solution]

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

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

[Anthony_Tonkin]

Thank you kindly. Very helpful indeed.