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
- Re: Skip empty field in formula

Topic Options

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

Solved

Jump to Solution

0
1366
0

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

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 08, 2022 02:26 PM

Hi Community,

I want to use a formula to calculate the age of a product

DATETIME_FORMAT(TODAY(),‘YYYY’) - {year produced}

Some fields of {year produced} are empty, so the formula comes back as the age of 2022 years old. Is there some way to exclude the empty {year produced} fields? The year produced, has the number field type.

Thanks in advance!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 08, 2022 07:00 PM

Welcome to the Airtable community!

Ben’s formula should get you part of the way to what you want. It will show a value only when the {year produced} field has a value.

However, it looks like you also want to calculate the age of the item. I suggest this formula.

```
IF(
{year produced},
YEAR( TODAY() ) - {year produced}
)
```

Note that I am using `YEAR()`

instead of `DATETIME_FORMAT()`

This is because `YEAR()`

returns a number, whereas `DATETIME_FORMAT()`

returns a text string. In this case, Airtable is converting that text string into a number for you, so the math still works. However, it is still good practice to avoid mixing text strings and numbers when doing math.

Reply

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 08, 2022 05:30 PM

Hey @Robbie_He! Welcome in!

Here’s the version of your formula that will produce this result:

```
IF(
{year produced},
DATETIME_FORMAT(
TODAY(),
'YYYY'
)
)
```

Here, the formula will only return a value if the `{year produced}`

field has a value in it.

Let me know if you have any trouble with that, or have any more questions!

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 08, 2022 07:00 PM

Welcome to the Airtable community!

Ben’s formula should get you part of the way to what you want. It will show a value only when the {year produced} field has a value.

However, it looks like you also want to calculate the age of the item. I suggest this formula.

```
IF(
{year produced},
YEAR( TODAY() ) - {year produced}
)
```

Note that I am using `YEAR()`

instead of `DATETIME_FORMAT()`

This is because `YEAR()`

returns a number, whereas `DATETIME_FORMAT()`

returns a text string. In this case, Airtable is converting that text string into a number for you, so the math still works. However, it is still good practice to avoid mixing text strings and numbers when doing math.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 09, 2022 02:18 AM

Reply