Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here
Apr 25, 2020 11:58 AM
How can I edit this formula to leave the field blank if the number returned is negative? Thanks!
IF({Update Cycle}=‘1’,({Days Since Update}-30),
IF({Update Cycle}=‘2’,({Days Since Update}-60),
IF({Update Cycle}=‘3’,({Days Since Update}-90),
IF({Update Cycle}=‘4’,({Days Since Update}-120),
IF({Update Cycle}=‘5’,({Days Since Update}-150),
IF({Update Cycle}=‘6’,({Days Since Update}-180),
IF({Update Cycle}=‘7’,({Days Since Update}-210),
IF({Update Cycle}=‘8’,({Days Since Update}-240),
IF({Update Cycle}=‘9’,({Days Since Update}-270),
IF({Update Cycle}=‘10’,({Days Since Update}-300),
IF({Update Cycle}=‘11’,({Days Since Update}-330),
IF({Update Cycle}=‘12’,({Days Since Update}-360),
“”
)
)
)
)
)
)
)
)
)
)
)
)
Apr 25, 2020 01:37 PM
There might be other ways to do this, but I would just do it as 2 different formula fields.
What you outlined above would give you the result in your 1st formula field.
Then, in your 2nd formula field, you would evaluate whether your 1st formula field was negative or positive. If it was negative, it would result in a blank field. If it was positive, it would show you the same result.
Apr 25, 2020 01:42 PM
The other way you could do it would be to wrap that entire large block of IF statements in yet another enclosing IF statement, which would essentially be evaluating the results of your large block of IF statements as the result to evaluate for the one “master surrounding IF statement”. So just put another IF statement AROUND your existing block of IF statements.
Apr 25, 2020 02:00 PM
To have a blank field instead of a negative numbers, wrap the formula in an IF
statement. You can also simplify your formula using a little math so that you do not need to nest so many IF
statements.
IF(
({Days Since Update} - (30 * {Update Cycle})) > 0,
{Days Since Update} - (30 * {Update Cycle})
)
Apr 25, 2020 05:56 PM
I’d like to use this simplified method, but i input what you wrote and it gave an error. Sorry, I’m a noob. I could do it either way, but I’m not even sure how to wrap the entire thing in an IF statement as suggested. Could anyone show me how that’s done on the original formula? Thanks!
Apr 25, 2020 08:42 PM
Are {Days Since Update} and {Update Cycle} both number fields, or are they some other type of field?
If they are not numbers, their values need to be converted to numbers in order to do math with them. Here are two options:
VALUE()
function in the formula field.VALUE({Days Since Update}}
VALUE({Update Cycle})
The formula that I provided already has the IF
statement to avoid displaying negative values.
The support page for formulas has more information on how formulas work, including using the IF
statement and doing basic calculations.
Apr 25, 2020 09:03 PM
Ok so I have changed the field type to Number, but not sure what formula to put in place of my original formula.
Apr 25, 2020 09:10 PM
Now that the field types are numbers, do you still get the error message with my first formula?
If so, can you provide a screen capture that shows all three fields?
Apr 26, 2020 12:32 PM
It’s working, thanks! Now one last thing, how can I make it leave the cell blank if my Update Cycle cell is empty? Right now it shows whatever number is in the Days Since Update cell if that cell is empty.
Apr 26, 2020 12:52 PM
You can use the AND
function to combine conditions for the IF
function:
IF(
AND(
NOT({Days Since Update} = BLANK()),
NOT({Update Cycle} = BLANK()),
({Days Since Update} - (30 * {Update Cycle})) > 0
)
{Days Since Update} - (30 * {Update Cycle})
)
Normally you can use just the {field name} in the condition, but because these are numeric fields, you need the NOT({field name} = BLANK()}
syntax.
Apr 27, 2020 11:16 AM
Thanks for this kuovonne, however, I am getting an error message when I insert that code. Is it correct?
Apr 27, 2020 01:14 PM
Sorry, there was a missing comma.
Sometimes I am typing away from my computer and cannot always test out the complete formula.
IF(
AND(
NOT({Days Since Update} = BLANK()),
NOT({Update Cycle} = BLANK()),
({Days Since Update} - (30 * {Update Cycle})) > 0
),
{Days Since Update} - (30 * {Update Cycle})
)
Apr 27, 2020 07:06 PM
This works, thanks for your help!