Skip to main content

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!

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…

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

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.





JB

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


That worked a treat! Thanks @JonathanBowen.

Reply


Terms & Conditions