Help

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

Solved
Jump to Solution
1305 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tom_R
6 - Interface Innovator
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).

58%20pm

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

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.

Screenshot 2019-05-29 at 07.29.43.png

JB

See Solution in Thread

4 Replies 4

Change the >1. to >0FIND() 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
6 - Interface Innovator
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)?

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.

Screenshot 2019-05-29 at 07.29.43.png

JB

That worked a treat! Thanks @JonathanBowen.