Is there a way to have a formula tell you if a deadline is upcoming instead of just before and after?

[Lauren_Azicri]

Hi all,

I am trying to format a field to show whether a deadline is “On Time” (before deadline due date), “At Risk” (same day as deadline due date) and “Late” (past deadline due date/time).

Is this possible? I keep getting lost in my nested if statements and when I add another component, I am getting blank records.

Thanks,
Lauren

[Kamille_Parks]

Try a formula like this one:

IF({Deadline Due Date} > TODAY(), "On Time", IF(Birthday < TODAY(), "Late", "At Risk"))

Let me know if this works for you. If it did, please mark it as the solution.

[Lauren_Azicri]

Kamille_Parks:

IF({Deadline Due Date} > TODAY(), “On Time”, IF(Birthday < TODAY(), “Late”, “At Risk”))

I was able to make this work to show “On Time” and “Late”, but the “At Risk” is what I am still looking for and having trouble with. I think it might have to do with the fact that I am using a Date Field with a time as the subject for the “IF” and I only want the time to come in to factor with Late. For instance, if the due DATE is before today, then it would show “On Time”, if the due DATE is today then it would show “At Risk” and if the due DATE/TIME is after now then it would show “LATE”.

[Kamille_Parks · Accepted solution]

Try this:

IF({Deadline Due Date} < NOW(), "Late", IF(IS_SAME({Deadline Due Date}, TODAY(), 'day'), "At Risk", "On Time"))

[Lauren_Azicri]

This seems to have worked! Thank you so much! I am going to make this as a solution, but I have a question -

When dealing with dates and < or > - is the further down the calendar we go make the date “greater”? I think that is where I was getting tied up because I am not familiar with how the dates greater than and less than work.

[Kamille_Parks]

Lauren_Azicri:

is the further down the calendar we go make the date “greater”?

Yes. Think of it this way: the further a date is from the beginning of time, the “greater” a date is. More time has passed, therefore the value of that date is greater than the days that came before.

Alternatively, you could have also used IS_BEFORE() or IS_AFTER() instead of < or >.

[Lauren_Azicri]

Thank you for your help with this along with this explanation - it is greatly appreciated and has helped understand calculating date more in Airtable!

[kuovonne]

Lauren_Azicri:

I keep getting lost in my nested if statements and when I add another component, I am getting blank records.

Yes, it is really easy to get lost in nested statements.

I find it easier if I use an external editor and write the formula with multiple lines:

IF(
  condition-1,
  first-true-result,
  IF(
    condition-2,
    second-true-result,
    IF(
      condition-3,
      third-true-result,
      final-else-result
    )
  )
)

This formatting makes it easier to spot errors:

• each IF statement and its closing `) are vertically aligned.
• each IF statement has exactly three lines at the next level of indent that match the three parameters to the IF statement.
• each line that represents a parameter ends with a , unless the following line is a )

For very complex formulas, I use a code editor with automatic bracket matching.

[Lauren_Azicri]

I sometimes find the vertical formulas hard to follow but it depends on the formula and how complex it is.

If you don’t mind me asking, what external editor do you use to figure out these formulas?

[kuovonne]

Vertical formulas can take a bit getting used to. Simple formulas can be easier to understand if they are written horizontally. However, the more complex a formula is, the more I find that writing them vertically is necessary for me to keep track of all the pieces.

Plus, there are multiple ways of writing things vertically. For example, some people write an if statement with the condition on the same line as the if( part.

Just use whatever method works for you. I only suggested this method because you mentioned getting lost in nested if statements.

I write simple (one line) formulas directly in the Airtable editor. I write short, multi-line formulas in whatever text editor I have handy (often the very basic Notepad that comes with Windows because it opens quickly). I write long, complex formulas in the free code editor Atom, but any code editor would work.