# Re: Formula for "Due" and "Past Due"

3251 1
cancel
Showing results for
Did you mean:
9 - Sun

I have a table of tasks. Each task has a due date in a date field.

I want to create a new formula field that looks at the due date and spits out:

Upcoming
Due Today
Past Due

Extra credit if the formula can spit out “Due Soon” when a task is 2 days away.

How do I make this happen?

36 Replies 36
9 - Sun

This will accomplish what you requested.

``````IF (
{Due}="",
"Not Set",
IF(
AND(
DATETIME_DIFF({Due},TODAY(),'hours') >= 12,
DATETIME_DIFF({Due},TODAY(),'hours') <= 48
),
"Due Soon",
IF(
IS_BEFORE({Due},TODAY()),
"Past Due",
IF (
IS_SAME({Due},TODAY()),
"Due Today",
"Upcoming"
)
)
)
)``````
9 - Sun

Wow @Chester_McLaughlin - you nailed it!

I was able to get “upcoming”, “today”, and “past” working on my own, but “soon” was beyond me.

Also you took into account tasks with no due date! I didn’t know that was possible.

I learned a lot just from seeing this formula work. Thank you!

@Chester_McLaughlin how would I modify this so it’s eastern standard time?

4 - Data Explorer

I’m starting to get the feeling that there’s more to Airtable than I even understand (like when I read this!). Where would I put that formula??? Sorry to sound so dumb but I’m pretty new at Airtable in general.

And here’s a quick glimpse of how to set a field to be of type “formula”

I haven’t tested this, however this should work for your timezone.

``````if (
{Due}="",
"Not Set",
IF(
AND(
DATETIME_DIFF(SET_TIMEZONE({Due},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') >= 12,
DATETIME_DIFF(SET_TIMEZONE({Due},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') <= 48
),
"Due Soon",
IF(
IS_BEFORE(SET_TIMEZONE({Due},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),
"Past Due",
IF (
IS_SAME(SET_TIMEZONE({Due},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),
"Due Today",
"Upcoming"
)
)
)
)``````
6 - Interface Innovator

I’m trying to do this formula, replacing {Due} with my field name {Print Due Date}, but I keep getting an error on the formula.

Here’s my video to show you what I’m doing: http://example.24hrtees.net/kL8J

I’d love some help.

7 - App Architect

@Chais_Meyer, I had the same problem when I copied and pasted the formula as written by @Chester_McLaughlin.

The layout of the formula is nice because the various IF statements can be identified clearly, along with the corresponding opening and closing parentheses. But, it seems that Airtable has a problem with spaces in the formula. When I removed the spaces, the formula worked fine.

4 - Data Explorer

@Chester_McLaughlin Thanks for the code, but i seems to be an error, i replace “Due” with “Deadline” and remove the spaces, but i still have an error in the formula, can you check the code?

Thanks!