Remove #Error and return N/A when a field is blank

[Sarah_Simpson]

I’d like to show N/A when the field is blank, at the moment I get #Error. Can anyone help with where would I add it into my formula? Here’s what we have so far -

IF(
(IS_AFTER({Hose Inspection Expiry Date}, DATEADD(TODAY(),14,‘days’))=1),
“ :thumbs_up: ”,
IF(
AND({Hose Inspection Expiry Date}>TODAY(),{Hose Inspection Expiry Date}<(DATEADD(TODAY(),14,‘days’))),
“ :rotating_light: ”,
IF(
{Hose Inspection Expiry Date}<TODAY(),
“ :skull_and_crossbones: ”,
IF(
{Hose Inspection Expiry Date}=BLANK(),
“NA”,
“NA”
)
)
)
)

[Justin_Barrett]

I suggest making the blank field check first. My hunch is that the error you’re seeing is due to some of the other functions trying to operate on what’s in that field when it doesn’t have a date. In your case you’d replace the “value if true” portion of the IF function with the other checks you want to make on that date, and put “N/A” as the result if it’s false.

Here’s my full tweaked version of your formula:

IF(
    {Hose Inspection Expiry Date},
    IF(
        (IS_AFTER({Hose Inspection Expiry Date}, DATEADD(TODAY(),14,'days'))=1),
        "👍",
        IF(
            AND({Hose Inspection Expiry Date}>TODAY(),{Hose Inspection Expiry Date}<(DATEADD(TODAY(),14,'days'))),
            "🚨",
            IF(
                {Hose Inspection Expiry Date}<TODAY(),
                "☠️"
            )
        )
    ),
    "N/A"
)

[Caolan]

You appear to have one too many IF statements (the double “NA” at the end is confusing). Try moving your Blank first and reducing it to three arguments (which will give you your four responses).

P.S. Love the emojis.

IF(
{Hose Inspection Expiry Date}=BLANK(),
“NA”,
IF(
{Hose Inspection Expiry Date}<TODAY(),
“ :skull_and_crossbones: ”,
IF(
({Hose Inspection Expiry Date}>DATEADD(TODAY(),14,‘days’)),
“ :thumbs_up: ”,
“ :rotating_light: ”
)
)
)

[Sarah_Simpson]

Thank you Justin & Caolan. Greatly appreciated!