Skip to main content

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 😀


Have a Good day !

Gad

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!

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!


Thank you very much !

It works like a charm…

Reply


Terms & Conditions