Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 31, 2021 12:16 PM
New to airtable and a novice to formulas, so please ignore my ignorance. I am trying to come up with a formula to apply a fee based on the difference in days between two fields. I have been unable to get it to work. I think I can just create a chart and calculate fee based on results, but I am trying to avoid that. Basically i an trying variations of
IF(DATETIME_DIFF({Cancellation Date},{Order Date},‘day’)=1,50,DATETIME_DIFF{Cancellation Date},{Order Date},‘day’)=2,75,)etc…
No mater what I try, I can get the first part of the formula to work, displaying the first result. I cant figure out how to continue the formula! I need to end up grouping 1-2,3-5,>5.
ANY help would be appreciated as I am going nuts!
Solved! Go to Solution.
Mar 31, 2021 06:06 PM
Welcome to the community, @AJ27! :grinning_face_with_big_eyes: The IF()
function only accepts three arguments:
To test multiple options, there are a couple of options. One is to nest functions inside each other, adding the next IF()
test as the third argument of the one before it. For example:
IF(condition1, value-if-true, IF(condition2, value-if-true, value-if-false))
Keep nesting for as many conditions as you need to test.
The problem is that this gets very long very quickly. If you’re always testing the same thing and just need to switch the output based on the result that you find, the SWITCH()
function is preferred. Its format is like this:
SWITCH(value-to-test, option1, output1, option2, output2, ... optional-default-output)
In your case, the beginning of your formula using SWITCH()
would look like this (split across multiple lines for clarity):
SWITCH(
DATETIME_DIFF({Cancellation Date}, {Order Date}, 'days'),
1,50,
2,75,
etc.
)
Mar 31, 2021 06:06 PM
Welcome to the community, @AJ27! :grinning_face_with_big_eyes: The IF()
function only accepts three arguments:
To test multiple options, there are a couple of options. One is to nest functions inside each other, adding the next IF()
test as the third argument of the one before it. For example:
IF(condition1, value-if-true, IF(condition2, value-if-true, value-if-false))
Keep nesting for as many conditions as you need to test.
The problem is that this gets very long very quickly. If you’re always testing the same thing and just need to switch the output based on the result that you find, the SWITCH()
function is preferred. Its format is like this:
SWITCH(value-to-test, option1, output1, option2, output2, ... optional-default-output)
In your case, the beginning of your formula using SWITCH()
would look like this (split across multiple lines for clarity):
SWITCH(
DATETIME_DIFF({Cancellation Date}, {Order Date}, 'days'),
1,50,
2,75,
etc.
)
Mar 31, 2021 08:26 PM
Yup, after posting I figured it out and kept nesting. Now I wish I waited as I did not realize there was a switch function! Woulda saved time. Gonna redo anyway as it’s cleaner. Thank you.
Mar 31, 2021 08:30 PM
Glad that I can help! I’m actually working on a course all about Airtable formulas. It’s taking a long time to produce, but I’m hoping that it will shed some light on a lot of Airtable’s formula system idiosyncrasies.