# Formula with empty value in field

Topic Labels: Formulas
1366 6
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi,

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

Thanks,
6 Replies 6
18 - Pluto

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

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

4 - Data Explorer

It doesn't work ::(

12 - Earth

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(
NOT({Effective end date})
),'In Progress')``````

18 - Pluto

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(),
)
),
"In Progress"
)``````

4 - Data Explorer

@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 »)

12 - Earth

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