# Same as an Excel dateif calc getting age

## Recommended Posts

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 by snekkis

##### Share on other sites

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 by comment

##### Share on other sites

Wonderful, and thanks!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...