Skip to main content

I am working on a field that pulls a result based on a number range.   I aim to convert the number of days from a person's hire date and an incident date to a set phrase such as "Less than 3 Months",  "4 Months to 1 Year", etc.   My first field converts the difference between their hire date and the incident date to the number of days.   My thought was to create a formula that says if the number of days is between 0-90, then the result is "less than three months," and so forth.   My fields are "Hire Date", "Incident Date", & "DateDiff".  The field I need to formulate is "Length of Service".   What is the best approach to get my outcome?  

Unfortunately you won't be able to use a SWITCH here and you'll need to use IFs like so:

IF(
AND({DateDiff} > 0, {DateDiff} <= 90),
"Less than 3 months",
IF(
AND(...
)

 

Unfortunately you won't be able to use a SWITCH here and you'll need to use IFs like so:

IF(
AND({DateDiff} > 0, {DateDiff} <= 90),
"Less than 3 months",
IF(
AND(...
)

 


I can't seem to add additional outcomes without getting an error.  Here is the formula that works but adding another IF gives it an error.   I am sure it is something simple.  Is it best to copy and paste one of the IF lines then change the details? 

 

IF(
  AND({LOS2} >= .0, {LOS2} <.26),
  "First 3 Months",

  IF(
    AND({LOS2} > .25, {LOS2} < 1.00),
    "Within 1 Year",

        IF(
      AND({LOS2} >= 1.00, {LOS2} < 2.00),
      "1 - 2 Years",

          IF(
          AND({LOS} >= 2.00, {LOS} < 3.00),
      "2 - 3 Years")
    )
  )
)

I can't seem to add additional outcomes without getting an error.  Here is the formula that works but adding another IF gives it an error.   I am sure it is something simple.  Is it best to copy and paste one of the IF lines then change the details? 

 

IF(
  AND({LOS2} >= .0, {LOS2} <.26),
  "First 3 Months",

  IF(
    AND({LOS2} > .25, {LOS2} < 1.00),
    "Within 1 Year",

        IF(
      AND({LOS2} >= 1.00, {LOS2} < 2.00),
      "1 - 2 Years",

          IF(
          AND({LOS} >= 2.00, {LOS} < 3.00),
      "2 - 3 Years")
    )
  )
)

Could you provide a screenshot of the error you're seeing?

Could you provide a screenshot of the error you're seeing?


Thanks for the response.  I got it figured out!   Do spaces affect how a formula operates?   Wendy

Thanks for the response.  I got it figured out!   Do spaces affect how a formula operates?   Wendy


Like `IF({Field Name} =        "Test",        "Yes"          , "No)`?  If so, nope, they won't affect how the formula operates

Reply


Terms & Conditions