# How to leave cell blank instead of #ERROR when date is missing

Topic Labels: Formulas
Solved
3552 7
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey everyone!

How do I create the statement to leave cell blank if a value is missing instead of #ERROR? I’ve read you should not use the BLANK() code?

Code for Days Left:

``````WORKDAY_DIFF(TODAY(),Starts)
``````

Code for Urgency:

``````IF(
{Days Left} < 0,
"🔥OVERDUE",
IF(
{Days Left} <= 20,
"🔴 ASAP",
IF({Days Left} < 40,
"🟡 Medium",
IF({Days Left} < 100,
"🟢 Low"
)
)
)
)
``````

1 Solution

Accepted Solutions
14 - Jupiter

Hi @esu,

You can just wrap your Urgency formula in another condition (aka, another `IF()` function), like this:

``````IF(
{Days Left},
IF(
{Days Left} < 0,
"🔥OVERDUE",
IF(
{Days Left} <= 20,
"🔴 ASAP",
IF({Days Left} < 40,
"🟡 Medium",
IF({Days Left} < 100,
"🟢 Low"
)
)
)
)
)
``````
7 Replies 7
14 - Jupiter

Hi @esu,

You can just wrap your Urgency formula in another condition (aka, another `IF()` function), like this:

``````IF(
{Days Left},
IF(
{Days Left} < 0,
"🔥OVERDUE",
IF(
{Days Left} <= 20,
"🔴 ASAP",
IF({Days Left} < 40,
"🟡 Medium",
IF({Days Left} < 100,
"🟢 Low"
)
)
)
)
)
``````
5 - Automation Enthusiast

Thanks so much! Why in the heck does that result in a blank cell?

I just realized that for the other column too.

``````IF(Starts, WORKDAY_DIFF(TODAY(),Starts))
``````

I can “translate” the logic of this sentence but not the above. But they both do the same thing.

``````IF(Starts = BLANK(), BLANK(), WORKDAY_DIFF(TODAY(),Starts))
``````
14 - Jupiter

@esu,

When you put just a reference to the field in the `IF()` function as the condition, the function tries to evaluate the value of the field itself and determine if that value is “truthy” or “falsey”.

Any value other than a `0` is a “truthy” value, and the `IF()` statement processes the true response.

A blank cell or a natural `0` is a “falsey” value, and the `IF()` statement processes the false response.

18 - Pluto

I recommend a small tweak to @Jeremy_Oglesby 's formula.

Instead of having the condition be this …

``````IF(
{Days Left},
``````

Make the condition this …

``````IF(
{Days Left} & "",
``````

Without this small change, the field will be blank both when {Days Left} is blank and when {Days Left} is zero. However, when {Days Left} is zero you actually want a value! Including `& ""` after the field name fixes this problem.

5 - Automation Enthusiast

Thanks so much! I’m getting a weird thing with the Workday Diff code (adding the & “” didn’t affect it’s math)

``````IF(Starts & "", WORKDAY_DIFF(TODAY(),Starts))
``````

where if the Starts Date is today it says 1 day left, and if its yesterday, it says -2
That math seems wrong to me…
I don’t actually know how to get zero :thinking:

18 - Pluto

Adding `& ""` would only affect the result when {Days Left} is zero. There will be no change in most cases.

`WORKDAY_DIFF` counts each day as a full day, so when the start and end date are the same, that still counts as 1 day (assuming that the date is a work day).

`WORKDAY_DIFF` will produce a zero when both dates are in the same weekend (depending on timezone issues).

7 - App Architect

@Jeremy_Oglesby, This is terrific! Thank you!

I just Googled and stumbled upon this (for a similar scenario) and this worked like a charm! 😉

THANK YOU! 😉

Bless you!