# Output first chronological month based on months listed in a lookup field

Topic Labels: Formulas
Solved
1311 4
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hi all,

My goal is to display in Field 2 the first occurring month (in a year) based on the pulled months from Field 1 (lookup field).

Rather than using date formulas (tables are a little more complicated), I’m trying to create a formula to capture with strings:

`IF(FIND("January",{Field 1})>1, "January,",IF(FIND("February",{Field 1})>1,"February,",IF(FIND("March",{Field 1})>1, "March,",IF(FIND("April",{Field 1})>1, "April,"))))`

However as you can see from the screenshot, my formula is outputting the farthest right month in the lookup field - first record is showing March when it should be February. My initial design was to use the nested IF formula to work chronologically… but doesn’t seem to be working as I intended.

For this example I’m only using four months of the year, but will build out the formula with the remaining 8 once I figure this out.

Any help appreciated!

1 Solution

Accepted Solutions
13 - Mars

Hi @Tom_R - your `{Field 1}` is an array, so `FIND()` won’t work on that correctly (it does produce an output which is why it is showing the second month but the output isn’t correct for your purposes). Convert the array to a string using:

`{Field 1} & ''`

And then apply your formula to this new field.

JB

4 Replies 4
13 - Mars

Change the `>1`. to `>0``FIND()` returns the location of the match with the first character in the string numbered from `1` rather than `0`. Otherwise, your formula should work fine…

6 - Interface Innovator

Hmmm… I’ve updated the formula, but still seems to output the same thing?

Updated formula:

`IF(FIND("January",{Field 1})>0, "January,",IF(FIND("February",{Field 1})>0,"February,",IF(FIND("March",{Field 1})>0, "March,",IF(FIND("April",{Field 1})>0, "April,"))))`

Perhaps because of the comma is Field 1 (rollup field)?

13 - Mars

Hi @Tom_R - your `{Field 1}` is an array, so `FIND()` won’t work on that correctly (it does produce an output which is why it is showing the second month but the output isn’t correct for your purposes). Convert the array to a string using:

`{Field 1} & ''`

And then apply your formula to this new field.

JB

6 - Interface Innovator

That worked a treat! Thanks @JonathanBowen.