Skip to main content
Solved

Skip empty field in formula

  • March 8, 2022
  • 3 replies
  • 49 views
R

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!

Best answer by kuovonne

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.

3 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • March 9, 2022

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
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • March 9, 2022

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.

R
R
  • Robbie_He
  • Author
  • New Participant
  • March 9, 2022

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!

kuovonne
Terms & ConditionsAccessibility statement