+2Hi,
Could you please help me with a formula
I have 3 values:
Estimated start date
Estimated end date
Effective end date
Today's date
I want to display "in progress", if I have no value in my effective end date cell and if today is inferior than estimated end date
I've tried the following formula but it doesn't work:
+31Try this? Not sure if I've correctly understood though, sorry!
IF(
AND(
{Effective End Date} = "",
IS_BEFORE(
TODAY(),
{Estimated End Date}
)
),
"In Progress"
)
+2Try this? Not sure if I've correctly understood though, sorry!
IF(
AND(
{Effective End Date} = "",
IS_BEFORE(
TODAY(),
{Estimated End Date}
)
),
"In Progress"
)
Hi @TheTimeSavingCo , Thank you for your reply.
It doesn't work ::(
+25Hi @TheTimeSavingCo , Thank you for your reply.
It doesn't work ::(
Hi,
Don't use 'BLANK()' in Airtable.
You can use simple IF({Field} to check 'non-emptiness'. Also, in operator IF you can omit third parameter, 'value if false', it is blank by default.
You can use such constructions:
IF({Some Field}, 'field is not empty' , 'it is empty')
IF({Some Field}, 'field is not empty')
IF(NOT({Some Field}), 'it is empty')
To conclude, you formula with nested IFs:
IF(IS_AFTER({Estimated end date (Deadline)},TODAY()),
IF(NOT({Effective end date}),'In Propgress')
)
or, with AND:
IF(AND(
IS_AFTER({Estimated end date (Deadline)},TODAY()),
NOT({Effective end date})
),'In Progress')
+31Hi @TheTimeSavingCo , Thank you for your reply.
It doesn't work ::(
Yeap, you need to update the formula to use your field names!
Here's a version of the formula updated with your field names:
IF(
AND(
{Effective End Date} = "",
IS_BEFORE(
TODAY(),
{Estimated end date (Deadline)}
)
),
"In Progress"
)
+2Hi, Many thanks @TheTimeSavingCo and @Alexey_Gusev for your reply!! Appreciate it!
@TheTimeSavingCo It works, and I now have 4 different formulas!
I'd like to congregate 4 formulas into 1! Could you help me please?
Substep in progress, on time: (no effective end date and today is before estimated end date):
IF(AND({Effective end date} = "",IS_BEFORE(TODAY(),{Estimated end date (Deadline)})),"In Progress, on time »)
Substep in progress, but late: (no effective end date and today is after estimated end date):
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}), « In progress, late")
Substep done, on time: effective end date is before estimated end date:
IF(IS_BEFORE({Effective end date},{Estimated end date (Deadline)}),"Done, on time")
Substep done, late: effective end date is after estimated end date:
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}),"Done, late »)
+25Hi, Many thanks @TheTimeSavingCo and @Alexey_Gusev for your reply!! Appreciate it!
@TheTimeSavingCo It works, and I now have 4 different formulas!
I'd like to congregate 4 formulas into 1! Could you help me please?
Substep in progress, on time: (no effective end date and today is before estimated end date):
IF(AND({Effective end date} = "",IS_BEFORE(TODAY(),{Estimated end date (Deadline)})),"In Progress, on time »)
Substep in progress, but late: (no effective end date and today is after estimated end date):
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}), « In progress, late")
Substep done, on time: effective end date is before estimated end date:
IF(IS_BEFORE({Effective end date},{Estimated end date (Deadline)}),"Done, on time")
Substep done, late: effective end date is after estimated end date:
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}),"Done, late »)
Hi,
In general, if it's not BEFORE, then it's AFTER. But also, dates might be equal
Thus, IF(IS_BEFORE({Effective end date},{Estimated end date (Deadline)}),"Done, on time") in not quite correct. If your Effective date = Estimated, it's still "On time" but IS_BEFORE will be false.
That's why I will use IS_AFTER in formulas.
So, starting with IF({Effective end date}, *value present* , *value not present*)
Present (last 2 in your list):
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}), 'Done, late', 'Done, on time')
Not Present:
IF(IS_AFTER(TODAY(),{Estimated end date (Deadline)}), 'In Progress, late', 'In Progress, on time')
Now let's pack them together:
IF({Effective end date},
IF(IS_AFTER({Effective end date},{Estimated end date (Deadline)}), 'Done, late', 'Done, on time'),
IF(IS_AFTER(TODAY(),{Estimated end date (Deadline)}), 'In Progress, late', 'In Progress, on time')
)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.