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?
May 27, 2018 07:23 PM
If you notice, there are 2 different types of double-quotes in the formula you pasted there - the “straight” kind after the initial “Due=“, and the “curved” kind throughout the rest of the formula.
I don’t know what the technical difference is, or where the difference comes from, frankly, but I’d suggest just going through the formula in your Airtable field and replace every double-quote in it with your double-quote keystroke. If that doesn’t work then I’m not sure what could be wrong, because the rest looks fine!
May 28, 2018 02:06 AM
I agree with @Jeremy_Oglesby :winking_face:
But I think you forgot to close your IF()
too …
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"
)
)
)
)
Other than that, Without knowing how your table looks like, I don’t see anything else :woman_shrugging:
May 28, 2018 08:44 AM
Agreed, when moving formulas between text editors it’s easy to pick up “smart” or “curly” quotes. These must be reverted to “dumb quotes” for the formulas to parse correctly. This trips me up all the time, and may be a common snag in design shops that actually prefer smart quotes in most use-cases.
Smart Quotes: “ ”
Dumb Quotes: " "
May 28, 2018 09:01 AM
Is there an actual keystroke difference there? Or is it just a difference in the engine that interprets your keystroke?
May 28, 2018 09:20 AM
It likely depends on each users OS and/or text editor. Many default to smart quote auto-substitution.
But yes, you can specify the Unicode character used via keystroke:
https://practicaltypography.com/straight-and-curly-quotes.html
May 29, 2018 06:06 AM
And FWIW, here’s our team’s (two-field) formula for Due/Past Due:
Field 1
IF({Auto-Status} = “Complete”, “ :white_check_mark: ”,
IF({Auto-Status} = “Ghost!”, “ :ghost: ”,
IF({Auto-Status} = “Print Job Ticket”, “ :fax: ”,
IF({Auto-Status} = “Cancelled”, “ :skull: ”,
IF({Auto-Status} = “On Hold”, “ :zzz: ”,
IF({Release Date} = 0, “Release Date Needed”,
DATETIME_DIFF({Release Date}, TODAY(), ‘days’)
))))))
Field 2
IF({Days From Release Date} > 12, “Soon”,
IF({Days From Release Date} = 12, “Next Week”,
IF({Days From Release Date} = 11, “Next Week”,
IF({Days From Release Date} = 10, “Next Week”,
IF({Days From Release Date} = 9, “Next Week”,
IF({Days From Release Date} = 8, “Next Week”,
IF({Days From Release Date} = 7, “Next Week”,
IF({Days From Release Date} = 6, “This Week”,
IF({Days From Release Date} = 5, “This Week”,
IF({Days From Release Date} = 4, “This Week”,
IF({Days From Release Date} = 3, “In Three Days”,
IF({Days From Release Date} = 2, “In Two Days”,
IF({Days From Release Date} = 1, “Tomorrow”,
IF({Days From Release Date} = 0, “Today :fire: ”,
IF({Days From Release Date} < 0, “Past Due :warning: ️”,
IF({Days From Release Date} = “Due Date Needed”, “Due Date Needed”,
“”))))))))))))))))
May 29, 2018 06:37 AM
This worked, Thank you so much!!!
Could it make it a lot more complicated to add another IF statement that if the Status says “done” then “When is it due” would say Completed instead of Past due?
May 29, 2018 07:41 AM
Nope:
IF( {Status} = "Done", "Completed",
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"
)
)
)
)
)
May 29, 2018 07:52 AM
You’re amazing, thank you so much for your help! I’m able to learn a lot through you guys, thank you for your time!
May 29, 2018 09:24 AM
I’m glad to know I could help :grinning_face_with_smiling_eyes: !
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")