Need help displaying result from calculating difference between two dates (from fields)

AJ27 · ‎Mar 31, 2021

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!

Justin_Barrett · ‎Mar 31, 2021

Welcome to the community, @AJ27! :grinning_face_with_big_eyes: The IF() function only accepts three arguments:

(Required) A condition/or expression that should return True or False (or their equivalent)
(Required) The output if the first argument returns True
(Optional) The output if the first argument returns False; if omitted, the output is null/blank

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.
)

See Solution in Thread

Justin_Barrett · ‎Mar 31, 2021

Welcome to the community, @AJ27! :grinning_face_with_big_eyes: The IF() function only accepts three arguments:

(Required) A condition/or expression that should return True or False (or their equivalent)
(Required) The output if the first argument returns True
(Optional) The output if the first argument returns False; if omitted, the output is null/blank

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.
)

AJ27 · ‎Mar 31, 2021

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.

Justin_Barrett · ‎Mar 31, 2021

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.