If cell contains formula

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?

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.

2 Likes

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.