Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 28, 2023 03:20 AM
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
Nov 28, 2023 03:52 AM
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"
)
Nov 28, 2023 05:20 AM
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!
Nov 28, 2023 03:20 PM
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"
)