We use cookies to improve your site experience and analyze website traffic. Click Accept to agree to our website's cookie use as described in our Cookie Policy . Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Specific help with an IF DURATION formula, then di...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Printer Friendly Page

Topic Labels:
Formulas

0
2403
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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!

5 Replies 5

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.

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

Reply

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