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?
Dec 11, 2016 11:00 AM
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"
)
)
)
)
Dec 11, 2016 01:26 PM
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!
Dec 12, 2016 03:01 PM
@Chester_McLaughlin how would I modify this so it’s eastern standard time?
Dec 14, 2016 04:07 PM
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.
Dec 14, 2016 04:15 PM
Here’s the best place to get familiar with the formula field type.
And here’s a quick glimpse of how to set a field to be of type “formula”
Dec 14, 2016 04:18 PM
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"
)
)
)
)
May 08, 2017 10:48 AM
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.
May 10, 2017 10:24 AM
@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.
Jun 30, 2017 05:02 PM
@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!