Skip to main content
Solved

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

A
  • AJ27
  • New Participant
  • 1 reply

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!

Best answer by Justin_Barrett

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.

)
View original
Did this topic help you find an answer to your question?

3 replies

J

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.

)
A
  • AJ27
  • Author
  • New Participant
  • 1 reply
  • April 1, 2021
Justin_Barrett wrote:

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.

)

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.

J
1 person likes this
  • J
    Justin_Barrett
J
AJ27 wrote:

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.


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.

Reply


Most helpful members this week

Terms & Conditions