Need help on IF formula

[JustineC]

Hi,

I want to create an IF formula but the result I get is not what I expected

Here is the formula: IF({Date effective de fin du cadrage (from Date effective de fin du cadrage)}<{Date estimée de fin du cadrage (from Date estimée de fin du cadrage)},"In time","Late")

I want to get: "In time" if the effective end date of the scoping is before the estimated end date of the scoping and I want to get "Late" if the effective end date of the scoping is after the estimated end date of the scoping

However, I'm getting "Late" whereas I should get 'In time".

Could you please help me? Thank you very much

[Sho]

Hi @JustineC,

Date and time comparisons require the use of a dedicated function.
IS_SAME(), IS_BEFORE(), IS_AFTER() and DATETIME_DIFF(). Read the reference for details.
Formula Field Reference - Overview | Airtable Support

For example, like this

IF(
  IS_AFTER(
    {Date effective de fin du cadrage (from Date effective de fin du cadrage)},
    {Date estimée de fin du cadrage (from Date estimée de fin du cadrage)}
  ),
  "In time",
  "Late"
)

[JustineC]

Hi, 

Many thanks for your quick reply!! That works!

I have another question for you, I'm really bad at adding multiple formulas into one. 

How could the one formula be with all those conditions:

No effective end date:

If today’s date is before estimated end date = in time

If today’s date is after estimated end date = late

Effective end date:

If effective end date is before estimated end date = in time

If effective end date is after estimated end date = late

Many thanks!

[Sho]

Formula syntax is easy once you learn the patterns.
If the field name is used as a condition, it can be used to determine if the field has a value.

In that case, like this.

IF(
  IF(
    {Date effective de fin du cadrage (from Date effective de fin du cadrage)},
    IS_AFTER(
      {Date effective de fin du cadrage (from Date effective de fin du cadrage)},
      {Date estimée de fin du cadrage (from Date estimée de fin du cadrage)}
    ),
    IS_AFTER(
      TODAY(),
      {Date estimée de fin du cadrage (from Date estimée de fin du cadrage)}
    )
  ),
  "In time",
  "Late"
)