Specific help with an IF DURATION formula, then display number or icon

Ask the community Formulas
#1

I’m lost! I’m trying to write a formula something like IF({Duration}>=5, “1”,{Duration}>=10, “2”), so when a contestant reaches a certain number of hours, a reward automatically appears in the column.

and so on…

Where duration is hours. I know AT defaults to seconds and have tried versions of this on my view. Just thought I’d throw out hours if it would work maybe.

I’ve tried: IF({Total Seconds Watched}>=18000, “1”,{Total Seconds Watched}>=36000, “2”)
with the current config. It seems to save the formula but then 1. only displays the number “1” even if seconds are greater than 36000, and 2. does not save the formula with the , “2” - it disappears each time I save it.

Help and thanks for any insights and more elegance to my clumsy formulating!

#2

Hi @Amocat_Staff,

The formula you have is incorrect as it has too many values. First of all, the formula cannot be based on >= 18000 because this means that in all cases it will return 1 (since 36000 is also bigger than 18000). i.e.: An IF formula will check the Logic first then it will return the Value 1 if true and Value 2 if false.

The way to do so would be either start from the Bigger Integer (i.e.: if more than 36000 first) then go down, or make the formula less than instead of more than. You can also add the Formula AND to your logic.

It will look something like this

IF(AND({Total Seconds Watched}>=18000, {Total Seconds Watched}<36000),1,IF({Total Seconds Watched}>=36000,2,0))

BR,
Mo

#3

As Mohamed pointed out, your formula does not have the right syntax. When you try to save an invalid formula, Airtable will try to figure it out.

  • If Airtable thinks it can figure it out, Airtable will save its revised version of the formula.
  • If Airtable is unable to figure it out, Airtable will give you an error message and not let you save.

You are probably running into the first situation. You are inputting

IF({Duration}>=5, “1”,{Duration}>=10, “2”)

However, since the IF function can have only three parameters, Airtable is probably saving

IF({Duration}>=5, “1”,{Duration}>=10)

This formula would return either 1 or 0.

  • when {Duration} is greater than or equal to 5, the function returns the string "1"
  • when {Duration is less than 5, the formula returns the “truthiness” of the expression {Duration}>=10), which would be false which is represented internally by 0.

To further complicate matters, if you are using a Duration field type (versus using the DATETIME_DIFF function, the stored value is always in seconds, even if the displayed value is in hours and minutes.

Thus, the field will display 1 for all durations greater than 5 seconds, which is the behavior you were seeing.

I hope this helps you to understand what was going on.

Now on to the formula that you actually want.

While Mohamed’s formula may be what you want, it can return any of three values (0, 1, or 2) but your original examples imply that you would like to return only two possible values (1 or 2).

If your {Duration} is in a formula field with a DATETIME_DIFF function that returns hours, use

IF({Duration} >= 10, 
  2,
  IF({Duration} >= 5, 1)
)

If your {Duration} is stored in a Duration field, use

IF({Duration} >= 36000, 
  2,
  IF({Duration} >= 18000, 1)
)

By the way, you can format your posts using Markdown.

Format inline code by surrounding it with backticks: ` Code `

Create a block of code by starting and ending the block with a series of three backticks: ```

1 Like
#4

Thank you for the input.

Hmmm, what if I want there to be up to 10 results from the input, will IF function not work? I’m trying to write the formula so that if a person watches X amount of films, they get Y free tickets where every 5 hours = one ticket.

Do I need to use DATETIME_DIFF? If so, how

Thanks in advance

#5

You can nest as many IF statements as you need.

However, if they get 1 free ticket for every 5 hours, you could create a formula to directly calculate the number of tickets.

INT( {hours} / 5)

Use INT to make sure the number of tickets is an integer, not a decimal.

#6

Of all the combinations I tried, I can’t believe I didn’t do this one. Works perfectly, thank you so much!