Feb 26, 2020 05:41 PM
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!
Feb 27, 2020 12:40 AM
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
Feb 27, 2020 11:30 AM
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.
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.
"1"
{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: ```
Mar 09, 2020 02:37 PM
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
Mar 09, 2020 03:22 PM
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.
Mar 09, 2020 03:33 PM
Of all the combinations I tried, I can’t believe I didn’t do this one. Works perfectly, thank you so much!