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?
Solved
IF or SWITCH functions with number ranges
+7Best answer by TheTimeSavingCo
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(...
)
+31Unfortunately 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(...
)
+7Unfortunately 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")
)
)
)
+31I 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?
+7Could 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
+31Thanks 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
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.