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

Topic Labels: Formulas
Solved
351 2
cancel
Showing results for
Did you mean:
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

1 Solution

Accepted Solutions
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+"
)
``````

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

2 Replies 2
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+"
)
``````

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

5 - Automation Enthusiast

Thank you kindly. Very helpful indeed.