Aug 07, 2019 02:23 PM
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.
Aug 09, 2019 08:04 AM
This is what I have coded thus far:
DATETIME_FORMAT({Date of Donation}, ‘MM/DD/YY’) & " | " & {First Name} & " " & {Last Name}
Aug 09, 2019 08:12 AM
I am also in the far left column coding this I don’t know if it makes a difference
Aug 09, 2019 12:00 PM
Yup, that’s exactly what my formula does. Here’s a screenshot:
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.
Aug 13, 2019 08:43 AM
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.
Aug 13, 2019 08:53 AM
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
)
)
Aug 13, 2019 09:18 AM
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
)
)
Aug 13, 2019 09:22 AM
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?
Aug 13, 2019 09:43 AM
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
)
Aug 13, 2019 09:47 AM
wow thank you so much for all your help
May 22, 2022 11:37 PM
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.
May 23, 2022 05:55 PM
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.
May 23, 2022 06:13 PM
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’))
)
May 23, 2022 06:20 PM
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"))