Help

Calculating YOB from Age (DOB = unknown)

Topic Labels: Formulas
Solved
Jump to Solution
1905 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Joan_Mershon
5 - Automation Enthusiast
5 - Automation Enthusiast

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’)

1 Solution

Accepted Solutions
pcbowers
6 - Interface Innovator
6 - Interface Innovator

@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.

See Solution in Thread

4 Replies 4

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'))

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.

pcbowers
6 - Interface Innovator
6 - Interface Innovator

@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.

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.