Skip to main content

Need help on IF formula

  • November 28, 2023
  • 3 replies
  • 32 views
J
Forum|alt.badge.img+1

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

2 Attachments

3 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • November 28, 2023

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" )

 

J
Forum|alt.badge.img+1
  • JustineC
  • Author
  • New Participant
  • November 28, 2023

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!

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • November 28, 2023

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" )

 

Terms & ConditionsAccessibility statement