Help

Re: Skip empty field in formula

Solved
Jump to Solution
1587 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Robbie_He
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

3 Replies 3

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!

kuovonne
18 - Pluto
18 - Pluto

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.

Thank you Kuovonne for you fast response! Your formula worked :grinning_face_with_big_eyes: , this will help me further!