Same as an Excel dateif calc getting age

[snekkis]

Hello,

I've been using this Excel formula:

=DATEDIF(DATE(MID(A2;LEN(A2)-6;2);MID(A2;LEN(A2)-8;2);LEFT(A2;LEN(A2)-9));TODAY();"y")

It has been used for calculating the age of a person from a social sec. number with 11 digits. The first six digits from the left is day of birth, day/month/year. Is it possible to get the same calculation in Filemaker?

Edited October 26, 2018 by snekkis

[comment]

Yes, of course it is possible. Try =

Let ( [
yy = Middle ( SSN ; 5 ; 2 ) ;
cc = If ( yy < 30 ; 2000 ; 1900 ) ;
birthdate = Date ( Middle ( SSN ; 3 ; 2 ) ; Left ( SSN ; 2 ) ; cc + yy ) ;
today = Get ( CurrentDate )
] ;
Year ( today ) - Year ( birthdate ) - ( today < Date ( Month ( birthdate ) ; Day ( birthdate ) ; Year ( today ) ) )
)

where SSN is the name of the field holding the social security number.

Note:

• The calculation must be unstored;
• Since you mention Excel, I have used the 2029 rule to determine the century of the 6-digit date; you can change that at will;
• The calculation returns the age in years (i.e. the number of complete years since the person's birthday) - so set the result type to Number.

Edited October 26, 2018 by comment

[snekkis]

Wonderful, and thanks!