Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Skip empty field in formula

Topic Labels: Formulas
Solved
Jump to Solution
409 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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!

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!