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?
Aug 02, 2017 02:51 AM
You’ve probably already figured this out, but as a breadcrumb for future searchers, it’s the two spaces following IF
on lines 1 and 13 causing the problem; there should be no space between ‘IF’ and the opening parenthesis, as so: IF(
. The rest of the white space – which, as @Christoff points out, makes the code much easier to read, can stay; Airtable ignores it.
Aug 24, 2017 02:31 AM
The problem is “”, if you look carefully when you copy from web and past to the airtable, it generate to another character not same with the " understand in airtable. Hope it help you. It took me ages to debug and look carefully.
Aug 29, 2017 09:32 AM
@Chester_McLaughlin This worked perfectly for me once I deleted the additional spaces. Now I am wondering if I can add one more piece to the formula. This would be based on another Checkbox Column (separate from the Due Column). In other words, if the checkbox column is checked, I want the Report Status to say “Received” instead of “Past Due”. Is that possible?
Aug 29, 2017 09:44 AM
Certainly. Here’s the basic format for checking the status of a checkbox field:
IF({My Checkbox Field}=TRUE(),"It is checked!!!","It is NOT checked")
And if you ever want to check the inverse, do:
IF({My Checkbox Field}=FALSE(),"It is NOT checked","It is checked!!!")
Or you could do:
IF(NOT({My Checkbox Field},TRUE()),"It is NOT checked","It is checked!!!")
Aug 29, 2017 10:15 AM
Thanks, @Chester_McLaughlin Chester. I was able to make this work when I made a new column. But
I want to imbed this into the original formula from the thread. Is that
possible? So in one column, there would be either Received, Upcoming, or
Past Due. Upcoming and Past Due are generated from the datetime diff and
Received would be like an override if I select the checkbox in that row.
Aug 29, 2017 10:31 AM
IF(
{Received}=TRUE(),
"Received",
IF(
{Deadline}="",
"Not Set",
IF(
AND(
DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') >= 12,
DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') <= 48
),
"Due Soon",
IF(
IS_BEFORE(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),
"Past Due",
IF (
IS_SAME(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),
"Due Today",
"Upcoming"
)
)
)
)
)
Aug 29, 2017 10:57 AM
@Chester_McLaughlin This did it. Very cool! Thanks a lot.
May 26, 2018 08:57 PM
Looks like I’m about a year late to the party but the code still doesn’t work for me :frowning:
I deleted all spaces and re-typed all "
Any other tips on how I could make this work?
May 27, 2018 03:25 AM
What happens to me a lot when working on formulas is I accidentally delete or forget a a comma and/or a bracket :grinning_face_with_sweat: .
It may be simply it.
Could you copy/paste here your corrected formula to see if … ? :winking_face:
May 27, 2018 06:36 PM
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”)