Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: If cell contains formula

Solved
Jump to Solution
1853 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MattWeber
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all, I have been searching for this answer and haven’t came up with anything that is clear enough thus guides me in writing this formula. Let me try to set it up…

I have a multi-select column (call it column A), which is a list of months. I have another column (call it column B) that uses a datetime_format formula to show the current month.

I want to write a formula in a third column that returns a value (nothing specific, whether it is yes/no, a number, etc.) if the current month (column B) is selected in column A. Is this possible?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Absolutely! Let’s break this down…

When looking at a multi-select field with a formula, it’s going to return a string with the selected items separated by commas. The commas really don’t come into play in this case, but just know that it’s a string, not an array.

I’ll sometimes use formula fields that are then referenced by other formula fields. In this case, though, I recommend taking this and rolling it into your final formula that looks for the current month in the list from the multiple-select field unless you’re also using this current-month-generating formula field for some other purpose. It just keeps the table design cleaner. I’ll show both options below.

The way to find the current month in the list of items from the multiple-select is by using the FIND() function. If the thing we’re trying to find exists in the target string, it’ll return its position in the string using a 1-based index; if not, it’ll return zero. (Another function that does something similar is SEARCH(), but it returns nothing if not found, so I’d prefer FIND() for this use case.)

I’ll use your generic field names for the examples below. First off, let’s assume that the formula for {Column B} is this:

DATETIME_FORMAT(NOW(), "MMMM")

That will give you a full month name: January, February, etc.

If you want to use a new formula field to find that month name from the items in {Column A}, that could look like this:

FIND({Column B}, {Column A}) != 0

That will return a 1 if the month name is found, and a 0 if it’s not found. As I said above, you could also wrap the month-creating formula directly into this, and nix {Column B} completely:

FIND(DATETIME_FORMAT(NOW(), "MMMM"), {Column A}) != 0

To take this a step further, if you want specific output based on whether or not the current month is found, you could do something like this:

IF(FIND(DATETIME_FORMAT(NOW(), "MMMM"), {Column A}), "Current", "Not Current")

If the FIND() function returns any non-zero number—meaning that the current month was found—you’ll see “Current”; otherwise you’ll see “Not Current.” Tweak as desired for your needs.

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Absolutely! Let’s break this down…

When looking at a multi-select field with a formula, it’s going to return a string with the selected items separated by commas. The commas really don’t come into play in this case, but just know that it’s a string, not an array.

I’ll sometimes use formula fields that are then referenced by other formula fields. In this case, though, I recommend taking this and rolling it into your final formula that looks for the current month in the list from the multiple-select field unless you’re also using this current-month-generating formula field for some other purpose. It just keeps the table design cleaner. I’ll show both options below.

The way to find the current month in the list of items from the multiple-select is by using the FIND() function. If the thing we’re trying to find exists in the target string, it’ll return its position in the string using a 1-based index; if not, it’ll return zero. (Another function that does something similar is SEARCH(), but it returns nothing if not found, so I’d prefer FIND() for this use case.)

I’ll use your generic field names for the examples below. First off, let’s assume that the formula for {Column B} is this:

DATETIME_FORMAT(NOW(), "MMMM")

That will give you a full month name: January, February, etc.

If you want to use a new formula field to find that month name from the items in {Column A}, that could look like this:

FIND({Column B}, {Column A}) != 0

That will return a 1 if the month name is found, and a 0 if it’s not found. As I said above, you could also wrap the month-creating formula directly into this, and nix {Column B} completely:

FIND(DATETIME_FORMAT(NOW(), "MMMM"), {Column A}) != 0

To take this a step further, if you want specific output based on whether or not the current month is found, you could do something like this:

IF(FIND(DATETIME_FORMAT(NOW(), "MMMM"), {Column A}), "Current", "Not Current")

If the FIND() function returns any non-zero number—meaning that the current month was found—you’ll see “Current”; otherwise you’ll see “Not Current.” Tweak as desired for your needs.

This is great! Thank you, I knew it was possible but couldn’t get the formula just right, I was close.