Forum Discussion

LG's avatar
LG
Qrew Member
2 months ago

IF statement changing numeric to text

I'm attempting to convert a numeric field to a text field based on the the content. So far I have the following formula but it's not working. 

IF(
[Time in Role] =< 1 ,"Less than 1 year",
[Time in Role] => 1.01 & =< 3 ,"1 to 3 years",
[Time in Role] => 3.01 & =< 5 ,"3 to 5 years",
[Time in Role] => 5.01 & =< 10 ,"5 to 10 years",
[Time in Role] => 10.01 & =< 15 ,"10 to 15 years",
[Time in Role] => 15.01 & =< 20 ,"15 to 20 years",
[Time in Role] => 20.01,"20+ years"
)

 

thanks!

  • What output are you getting in the field?  I do see some issues with the formulam but would like to know what results you are currently seeing.  Also, what is the field type for Time in Role?

    Initial observation:
    Condition 1 should be [Time in Role] < 1 if you are looking for less than a year.  currently you will get 1 year and less

    The other conditions should follow this format:
    [Time in Role] > year and [Time in Role] <= end year

    This is untested, but should get you pointed in the right direction

     

3 Replies

  • What output are you getting in the field?  I do see some issues with the formulam but would like to know what results you are currently seeing.  Also, what is the field type for Time in Role?

    Initial observation:
    Condition 1 should be [Time in Role] < 1 if you are looking for less than a year.  currently you will get 1 year and less

    The other conditions should follow this format:
    [Time in Role] > year and [Time in Role] <= end year

    This is untested, but should get you pointed in the right direction

     

    • LG's avatar
      LG
      Qrew Member

      I'm not getting any output, only syntax error. This helped, thanks!

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      try this

      IF(
      [Time in Role] =< 1 ,"Less than 1 year",
      [Time in Role] <= 3 ,"1 to 3 years",
      [Time in Role] <= 5 ,"3 to 5 years",
      [Time in Role] <=10 ,"5 to 10 years",
      [Time in Role] <= 15 ,"10 to 15 years",
      [Time in Role] <= 20 ,"15 to 20 years",
      [Time in Role]  > 20,"20+ years"
      )