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.
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.
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.
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!
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.
Mar 09, 2022 02:18 AM
Thank you Kuovonne for you fast response! Your formula worked :grinning_face_with_big_eyes: , this will help me further!