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

1606 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
4 - Data Explorer

This is a little of an old thread, but anyone looking for a more fleshed out solution, this is what i’ve come up with. I’ve included numbers in the output so that using the “sort” or “group” functions, they sort in the order i want. I probably could have ordered my IF statements more in line with the numbering too, but it gets confusing if I edit it too much (i’m fairly new to this!) Anyway, you should be able to simply copy and paste this into an Airtable formula, after setting a “Due” Date-type field. Here it is:

``````IF(
{Due}="",
"7.Not Set",
IF(
AND(
DATETIME_DIFF({Due},TODAY(),'hours') >= 12,
DATETIME_DIFF({Due},TODAY(),'hours') <= 48
),
"3.Due Soon",
IF(
AND(
IS_BEFORE({Due},TODAY()),
{Progress Status}!="Markups Complete - Ready for Review"
),
"1.Past Due",
IF(
{Progress Status}="Markups Complete - Ready for Review",
"6.Complete",
IF(
IS_SAME({Due},TODAY()),
"2.Due Today",
IF(
WEEKNUM({Due})=WEEKNUM(TODAY()),
"4.This Week",
IF(
WEEKNUM({Due})=(WEEKNUM(TODAY())+1),
"5.Next Week",
"In The Future"
)
)
)
)
)
)
)``````
4 - Data Explorer

Thank you so much from the future :joy: this helped me fix it!

4 - Data Explorer

I was able to successfully use this code but now I’m getting a “Past Due” alert for projects that have a status of complete. Is there a way to trigger a new status of “Completed” in the place of “Past Due” that is dependent on a field that is not the date field? In my table I use a Due Date Status column (where this code is located) and Project Status column that is updated by the user.

4 - Data Explorer

it’s the problem with quotation. you have to change in airtable? even for text value. like “due date”…

4 - Data Explorer

I have also managed to use this formula for a product management airtable - thanks. What is working is a column that uses formulas against the UAT estimate date (no estimate yet, upcoming, due soon and due today). However I need to add into the same column a way of checking the date delivered column to show a “done on time” status if date delivered is less than UAT estimate or “Delivered late” status if date delivered is greater than UAT date.

4 - Data Explorer

@Jeremy_Oglesby I have a similar question but I want something like: IF({Due date} > TODAY(), AND {Status} != “Complete”, “PAST DUE”) (please pardon my toddler-babble!) Thank you!

16 - Uranus

Proper usage of the `AND()` function is show further up in this thread.

`IF(AND({Due date} > TODAY(), {Status} != "Complete"), "PAST DUE")`