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: ```