Formula for task phase

[Emma_Sousa]

Hi Everyone,

I just can’t figure the formula thing out and I’ve spent hours on this, so am hoping that someone can help. Please if you respond - know that I’m not a programmer or formula savvy. I’m struggling with this.

I have a column called Follow-Up Date. I create a column called Task Status. I want to do the following, so the Task Status column populates with Planning, Due Soon, Wrap it up, Due Today and Overdue based on date criteria of the Follow-Up date.

1. If Follow-up Date is 2 weeks from the follow-up date based on today’s date, then I want the field to show “Planning”

2. If Follow-Up date is 1 week from the follow-up date based on today’s date, then I want the field to show “Due Soon”

3. If Follow-up date is 3 days from the follow-up date based on today’s date, then I want the field to show “Wrap it up”

4. If Follow-Up date is today, then I want the field to show “Due Today”

5. If Follow-Up date is past the date, then I want the field to show “Overdue”

I"ve played with many many examples to try and nail this…such as what I have below…but nothing works. The example below is just one scenario I tried, but what I listed above is really what I’m after. If anyone can help, I’d be extremely grateful.

Emma

IF(DATETIME_DIFF({Follow-Up Date},TODAY(),‘days’)>=3,DATETIME_DIFF({Follow-Up Date},TODAY(),‘days’)<=8),“Due Soon”,IF(IS_BEFORE({Follow-Up Date },TODAY()),“Past Due”,IF(IS_SAME({Follow-Up Date },TODAY()),“Due Today”,“Upcoming”))))

[Jeremy_Oglesby]

Hi @Emma_Sousa – I’ll take a stab at it. Give this a try and see if it does what you want:

IF(
  DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') > 7,
  "Planning",
  IF(
    DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') > 3,
    "Due Soon",
    IF(
      DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') > 0,
      "Wrap it up",
      IF(
        DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') = 0,
        "Due Today",
        "Overdue"
      )
    )
  )
)

The trick is to order the conditional statements in order of descending thresholds. The first threshold is > 7 days, and anything in that range returns “Planning”. If it’s < 7 days, then the next threshold it checks is > 3 days, and if that is met then it returns “Due Soon”… and so on.

[Emma_Sousa]

Hi Jeremy

Wow this is the closest I’ve gotten…so thanks for that. Using your data as is put info into my Task Status column, but it seems to be not displaying the right info. I have 3 lines to use as examples…in the screenshot you will see that the date of 3/5/20 should show as Planning…but it shows as overdue (this is greater than 7 days). The next date of 2/18/20 is greater than 3 days…so should show as Due Soon, yet it shows as overdue. Am I misunderstanding something?

I tried to change >7 line…changed ‘days’ to ‘weeks’, then change >7 to =1 but that did nothing to change the results.

image|533x440

I then tried to only modify the numbers only and changing days to weeks and the Task Status column did not change at all. What I changed is below.

IF(

DATETIME_DIFF(TODAY(), {Follow-Up Date}, ‘weeks’) = 2,

“Planning”,

IF(

DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'weeks') = 1,

"Due Soon",

IF(

  DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') = 3,

  "Wrap it up",

  IF(

    DATETIME_DIFF(TODAY(), {Follow-Up Date}, 'days') = 0,

    "Due Today",

    "Overdue"

  )

)

)

)

[Jeremy_Oglesby · Accepted solution]

Nope, you’re not misunerstanding anything, Emma :slightly_smiling_face:
I just got my terms backwards is all – sorry about that! Let’s give this a shot:

IF(
  DATETIME_DIFF({Follow-Up Date}, TODAY(), 'days') > 7,
  "Planning",
  IF(
    DATETIME_DIFF({Follow-Up Date}, TODAY(), 'days') > 3,
    "Due Soon",
    IF(
      DATETIME_DIFF({Follow-Up Date}, TODAY(), 'days') > 0,
      "Wrap it up",
      IF(
        IS_SAME({Follow-Up Date}, TODAY()),
        "Due Today",
        "Overdue"
      )
    )
  )
)

[Emma_Sousa]

Jeremy
Thanks so much…this is such a win. Not sure where I was messing up. I am curious however, when I look at how this formula displays in your response…I see it shows like typed with a return and tabs…stepping the formula for each item I needed. And it looks like there are spaces after the commas and in other areas. Could you education me just a bit if this is required? When I did mine, I had no spaces except when I typed my text in quotes. Maybe that was part of my issue in getting this to work.

Thanks again!!!
Emma

[Jeremy_Oglesby]

You’re welcome, @Emma_Sousa.

The formatting I used in the formula is not required in order for it to work. Spacing is entirely optional in Airtable formulas, and Airtable’s built in formula editor doesn’t support newlines (enter key) or tabbing anyways. It’s just a convention I use in the forum to make it easier for people to see the structure of and read formulas.

I also usually write my own formulas in a coding text editor that supports newlines and tabs so that I can write them out in this structured fashion and reason about them more easily, and then I copy and paste them into Airtable. Writing long, complex formulas in Airtable’s built-in editor is not a fun experience! But you can still just copy-paste them as is, and Airtable’s formula field will inherently ignore the newlines and tabs (you don’t have to manually remove them to make the formula work). Airtable’s formula field also inherently ignores white space, so spaces are irrelevant (their presence or their absence) except inside of quotes, which you have discovered already.

You may have also discovered already that you can format formulas with “code” formatting here in the forums by surrounding the whole block of text with three back-ticks on either side (top and bottom, ```).

When I write my formulas for Airtable, I use a free coding text-editor called Visual Studio Code. There are a couple other good, free editors out there like Atom or Notepad++. It doesn’t matter which one you use for this kind of thing – they all offer the same basic functionality: launch the editor, ctrl or cmd + N to create a new, blank document, and start typing your Airtable formula. The advantages you get are auto-completion of closing parentheses and brackets when you type the opening parenthesis or bracket, and auto tabbing in a hierarchical manner when you press enter inside of a set of parentheses. The things you lose are the auto-generated references to your fields and to Airtable’s built in functions.