Skip to main content
Solved

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

  • July 6, 2021
  • 7 replies
  • 158 views
E
Forum|alt.badge.img+3
  • esu
  • Participating Frequently

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!

Best answer by Jeremy_Oglesby

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

J
Forum|alt.badge.img+18

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"
           )
        )
    )
  )
)
E
Justin_Barrett
E
Forum|alt.badge.img+3
  • esu
  • Author
  • Participating Frequently
  • July 7, 2021

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

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))
J
Forum|alt.badge.img+18

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

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

E
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • July 7, 2021

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

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.

E
Justin_Barrett
J
E
Forum|alt.badge.img+3
  • esu
  • Author
  • Participating Frequently
  • July 7, 2021

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.


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:

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • July 8, 2021

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:


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

E
Heather_Hale
Forum|alt.badge.img+19
  • Heather_Hale
  • Inspiring
  • February 6, 2024

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

@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!

Terms & ConditionsAccessibility statement