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

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

Thank you in advance!

Hi @Ellen_Su,

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

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

@Ellen_Su,

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.

1 Like

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.

3 Likes

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:

image

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

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.