Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 10, 2016 03:10 PM
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?
Oct 02, 2018 11:01 AM
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" ) ) ) ) ) ) )
May 18, 2020 08:21 AM
Thank you so much from the future :joy: this helped me fix it!
Aug 12, 2020 07:35 AM
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.
Oct 18, 2020 07:22 AM
it’s the problem with quotation. you have to change in airtable? even for text value. like “due date”…
Feb 19, 2021 10:19 AM
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.
Feb 26, 2021 06:41 AM
@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!
Feb 26, 2021 09:03 AM
Proper usage of the AND()
function is show further up in this thread.
IF(AND({Due date} > TODAY(), {Status} != "Complete"), "PAST DUE")