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

[Tom_R]

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!

[W_Vann_Hall]

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…

[Tom_R]

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)?

[JonathanBowen · Accepted solution]

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

[Tom_R]

That worked a treat! Thanks @JonathanBowen.