Calculate Dates to display notice for Deadline

[Pierre-Yves_Gad]

Hello everyone,

I’m new to Airtable and I’m struggling to come up with a formula that calculates the different scenarios in relation to the timeframes for the tasks to be performed.

I would like to post a notice describing the situation in relation to the various remaining timeframes.

I am here so far, but i have an error that prevent notices to be displayed and I am lost… :

IF( Deadline = BLANK(),
  /* Deadline is not entered */
  "To be planned...",
  /* Deadline is entered */
  SWITCH( {End date} != BLANK(),
    DATETIME_DIFF( Deadline, {End date}, 'days' ) >= 0, "DEADLINE IS TODAY !",
    DATETIME_DIFF( Deadline, {End date}, 'days' ) < 0, DATETIME_DIFF({Délai}, TODAY(), 'days') & " DAYS LATE !"),
    DATETIME_DIFF( Deadline, {End date}, 'days' ) > 0,
    "Delivered on time"
  )
)

Can anyone help? I would be very grateful :grinning:

Have a Good day !
Gad

[AlliAlosa · Accepted solution]

Hi there, @Pierre-Yves_Gadina!

I think you are on the right track. At first glance, I see a couple problems. First, it’s best to avoid using BLANK() in formulas. It’s a known issue that it sometimes doesn’t evaluate properly.

Airtable will inherently evaluate whether a field is blank or not by just referencing the field name. For example…

IF({Deadline}, ...

is the same thing is as saying…

IF({Deadline} != BLANK(), ...

Next, I don’t think a SWITCH() statement will work for this formula. Unfortunately, you can’t pass a condition to evaluate whether the value matches or not; you need to compare a single value to another. I would change this to an IF() statement.

To simplify a bit, I added a field called {Days Overdue} with the following formula:

IF({Deadline}, DATETIME_DIFF(IF({End Date}, {End Date}, TODAY()), {Deadline}, 'days'))

This gives the number of days overdue, or a negative value if the date is in the future. You can then refer to this in your longer formula to keep it a bit easier to follow.

IF({Deadline}, IF(AND({End Date}, {Days Overdue} <= 0), "Delivered On Time", IF({Days Overdue} = 0, "Deadline is Today!", IF({Days Overdue} > 0, {Days Overdue} & " DAYS LATE !"))), "To be planned...")

I hope this helps!

[Pierre-Yves_Gad]

Thank you very much !
It works like a charm…