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.
May 17, 2021 02:12 PM
Slightly different from the other many birthday calculations.
I have AGE(date of record opened) and need to calculate AGE(Current). I do NOT have a birthdate and can’t get it.
How do I calculate - DateCreated - XX(years) = YOB
Which would allow me to then use -
DATETIME_DIFF(TODAY(), {YOB}, ‘years’)
Solved! Go to Solution.
May 18, 2021 06:31 AM
@Joan_Mershon There’s not an easy way to do this, unfortunately, unless you have more information. You could do a rough calculation: if you assume that the person’s birthday is the same day as when the data is entered, you can have a formula that adds 1 for every year that passes after the ‘Created Date’ passes.
However, this could be grossly inaccurate. If they are 21 and turn 22 tomorrow, they will appear as 21 until a year from that date even though they were 22 for 364 days. This means you will only be able to get an estimate. Thankfully, you will never overestimate someone’s age, just underestimate it. Therefore, you will be at maximum 365 days off from the correct age of the user.
If this amount of error is acceptable, here is the formula you are looking for:
{Age} + DATETIME_DIFF(TODAY(), CREATED_TIME(), "years")
If that error amount is not acceptable, you will need to start asking for someone’s birthday instead of their age. That way you can calculate age dynamically.
May 17, 2021 02:31 PM
Assuming {AGE(date of record opened)}
gives the time difference between birth/origination and record creation in number of years:
{YOB}
field’s formula:
IF({DateCreated}, DATEADD({DateCreated}, -{AGE(date of record opened)}, "years"))
*Note: {YOB}
would return a full date, not a year as the acronym may imply.
{AGE(Current)}
field’s formula:
IF({YOB}, DATETIME_DIFF(TODAY(), {YOB}, 'years'))
May 17, 2021 10:22 PM
Okay - it isn’t working and I suspect maybe in my efforts to explain clearly, I muddied the waters.
Record includes AGE - which is the age of the person at the time the data was entered.
I need to calculate a Current Age which will change over time.
I was trying by using a YOB field, but as long as I get a Current Age, I don’t care how it actually happens.
May 18, 2021 06:31 AM
@Joan_Mershon There’s not an easy way to do this, unfortunately, unless you have more information. You could do a rough calculation: if you assume that the person’s birthday is the same day as when the data is entered, you can have a formula that adds 1 for every year that passes after the ‘Created Date’ passes.
However, this could be grossly inaccurate. If they are 21 and turn 22 tomorrow, they will appear as 21 until a year from that date even though they were 22 for 364 days. This means you will only be able to get an estimate. Thankfully, you will never overestimate someone’s age, just underestimate it. Therefore, you will be at maximum 365 days off from the correct age of the user.
If this amount of error is acceptable, here is the formula you are looking for:
{Age} + DATETIME_DIFF(TODAY(), CREATED_TIME(), "years")
If that error amount is not acceptable, you will need to start asking for someone’s birthday instead of their age. That way you can calculate age dynamically.
May 18, 2021 10:20 AM
Awesome! That gives me just what I need. The formula works beautifully.
I understand it has a potentially 364day inaccuracy built-in, but for this project that is acceptable.