Help

Re: IF statement, when there is a blank cell

2384 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jocelyn_Piazza
5 - Automation Enthusiast
5 - Automation Enthusiast

If the cell is BLANK I want it to show up as blank in the field rather than ERROR

For example: IF ({Date of Donation} = BLANK()," “) but this isn’t working and right now I do not have the if statement so it is showing up as error.

I also want another IF statement that says : IF {{First Name} & {Last Name} = BLANK(), " {Organization }"}

So if the first name and last name are blank enter the organization name instead.

32 Replies 32

This is what I have coded thus far:

DATETIME_FORMAT({Date of Donation}, ‘MM/DD/YY’) & " | " & {First Name} & " " & {Last Name}

I am also in the far left column coding this I don’t know if it makes a difference

Yup, that’s exactly what my formula does. Here’s a screenshot:

Screen Shot 2019-08-09 at 1.57.55 PM.png

My version is in the first field, so that’s not the issue.

What exactly is (or isn’t) happening when you try to use the formula I wrote? Do you see an error message? Do you see a different result than what you expect? If so, what is it? The more details you can provide, the easier it will be to help.

Yeah that’s exactly what I want it to do. Except sometimes there is no date. maybe that’s why it doesn’t work?

It just says error invalid and it won’t let me enter it. Thats exactly what I have my fields as too.

Just to verify this is the full code that should work?

DATETIME_FORMAT({Date of Donation}, ‘MM/DD/YY’) & " | " & {First Name} & " " & {Last Name} IF(
{Date of Donation},
DATETIME_FORMAT({Date of Donation}, ‘MM/DD/YY’) & " | " &
IF(
AND({First Name},{Last Name}),
{First Name} & " " & {Last Name},
Organization
)
)

No, only this code should be used:

IF(
    {Date of Donation},
    DATETIME_FORMAT({Date of Donation}, 'MM/DD/YY') & " | " &
    IF(
        AND({First Name},{Last Name}),
        {First Name} & " " & {Last Name},
        Organization
    )
)

Great!

This works, thanks so much.

But I have one more question. If the cell doesn’t have an org name or a date nothing is showing up, I would like for at least their first and last name to show up?

This variation should take care of that. If the date is there, it will format it before the person/org name, whichever is available. If no date, the name will still appear.

IF(
    {Date of Donation},
    DATETIME_FORMAT({Date of Donation}, 'MM/DD/YY') & " | "
) & IF(
    AND({First Name},{Last Name}),
    {First Name} & " " & {Last Name},
    Organization
)

wow thank you so much for all your help

Tito_Alverio
6 - Interface Innovator
6 - Interface Innovator

I was also interested in ignoring blank fields and found that CONCATENATE() might be a simpler formula to use for what you want. Just putting the columns inside the parentheses will ignore blanks and move on to the next input. I did have to include an IF() for my Date column that uses a specific DATETIME_FORMAT() because when DATETIME_FORMAT is inside CONCATENATE it would throw an error if empty. But the IF formula was the solution to that problem.

Welcome to the community, @Tito_Alverio! :grinning_face_with_big_eyes: While the CONCATENATE() function could be used to do what you describe, it doesn’t do anything that the & concatenation operator can’t do, and “&” is a lot faster to type. Get rid of the CONCATENATE() function wrapper, replace the commas between items with the “&” operator, and you have the same result.

I get ERROR if the field happens to be blank when using “&”. Someone on my team notified me to use CONCATENATE as it ignores blank fields.

This got me the result I was looking for and no “ERROR” when the field is blank. (The first two fields in the formula are usually always filled out by my department, but the last two may be blank and I wanted a way to make sure the formula still worked)

CONCATENATE(Brand, " ", Show, " ", {Execution or Sub-Show}, " ", IF({Shoot Date},DATETIME_FORMAT({Shoot Date}, ‘YYYY-MM-DD’))
)

You didn’t mention which field you’re referring to when talking about “the field” (the one that’s blank), but I’m guessing that you’re talking about a date field. What’s solving your problem is the IF() function that you’re using. It’s not a “&” vs CONCATENATE() issue. Here’s that same formula rewritten to use the “&” operator:

Brand & " " & Show & " " & {Execution or Sub-Show} & " " & IF({Shoot Date}, DATETIME_FORMAT({Shoot Date}, "YYYY-MM-DD"))

The only problem with this is that you have a trailing space if the date isn’t appended to the end. Here’s a variation that only adds the separating space if the date is present:

Brand & " " & Show & " " & {Execution or Sub-Show} & IF({Shoot Date}, " " & DATETIME_FORMAT({Shoot Date}, "YYYY-MM-DD"))