Formula with empty value in field

[JustineC1]

Hi, 

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: 

IF(AND(({Effective end date})=BLANK(), {Estimated end date (Deadline)} > TODAY()), "In progress")

Could you help me please?

Thanks,

[TheTimeSavingCo]

Try this?  Not sure if I've correctly understood though, sorry!

IF(
  AND(
    {Effective End Date} = "",
    IS_BEFORE(
      TODAY(),
      {Estimated End Date}
    )
  ),
  "In Progress"
)

[JustineC1]

Hi @TheTimeSavingCo , Thank you for your reply.

It doesn't work ::( 

[Alexey_Gusev]

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

[TheTimeSavingCo]

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

[JustineC1]

Hi, 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 »)

[Alexey_Gusev]

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