Calculate age from number of digits

[FysiOlsen]

Hi.

 

I am trying to create a calculation field that calculates a persons age from a line of digits (danish social security number).

 

I have a "Cpr nr" text field where the data is entered as: ddmmyy-xxxx (120475-xxxx meaning the person is born April 12th 1975)

I have created a calculation field, called "Age" where I am trying to show the persons age.

 

I have looked through several solutions but can´t get it right. I know there is at least a problem with the year since it is only typed with the last to digits in "Cpr nr"

 

Here is my calculation:

 

Truncate (( Get ( CurrentDate ) - GetAsDate (Left( Cpr nr; 3 ; 2 ) & "/" & Left( Cpr nr ; 2 ) & "/" & Left( Cpr nr ; 5 ; 2 )) ) / 365.25,0) = Age

 

Any help is appriciated, thanks.

[Wim Decorte]

instead of using GetAsDate, use the Date() function.  That should be more reliable.

[eos]

To parse out the date:

Let ( [
ssn = Cpr nr ;
m = Middle ( ssn ; 3 ; 2 ) ;
d = Left ( ssn ; 2 ) ;
y = Middle ( ssn ; 5 ; 2 ) ;
y = y + case ( y < 20 ; 2000 ; 1900 )
] ;
Date ( m ; d ; y )
) 

You see that having the year not in 4 digits may pose a problem; 20 is just an arbitrary offset and will break should you have some really senior citizens …

 

There are several threads in this forum dedicated to calculating an age (or simply a difference between two dates), as well as numerous CFs, e.g. this one: http://www.briandunning.com/cf/518

[FysiOlsen]

Hi Win and eos.

 

Thank you for trying to help here.

I have already seen http://www.briandunning.com/cf/518 as it was almost what I was looking for. It was unfortunately still a little too complicated for me to figure out how to adapt it to my needs, as I am still a very newbie being on my third week with Filemaker.

Wim: I don´t know how to write the calculation, but I will do some more trying to put things together the right way.

 

Any further help is appriciated.

[comment]

When you use GetasDate ( dd/mm/yy ), Filemaker has to guess the century. The method used for the guess is actually kinda smart, however in your case it would seriously backfire. I don't know at what age a Danish citizen is issued a social security number, but I am willing to bet it's not before he or she is born...

So I would suggest you make your own assumption - for example, let's say that the person must be at least 16 years old. Then their age would be =
 

Let ( [
d1 = Left ( Cpr nr ; 2 ) ;
m1 = Middle ( Cpr nr ; 3 ; 2 ) ;
y1 = Middle ( Cpr nr ; 5 ; 2 ) ;

today = Get (CurrentDate) ;
y2 = Year ( today ) ;
c2 = Div ( y2 ; 100 ) ;

y1 = c2 * 100 + y1 ;
age = y2 - y1 - ( today < Date ( m1 ; d1 ; y2 ) )
] ;
age + 100 * ( age < 16 )
)

The result type is Number and the calculation must be unstored.


Note that by saying that a person must be at least 16 years old you are also saying that a person cannot be older than 115 years; there is simply no way to accommodate both without getting some more data at the input.

[FysiOlsen]

Thank you comment!

That did the trick. I definitely wouldn´t have gotten this far in three weeks without all the help I get here!

[comment]

Well, really.. Why don't you do your homework? I was reading something on Wikipedia and as will often happen, one link lead to another and so I stumbled upon this:
 

It is a ten-digit number with the format DDMMYY-SSSS, where DDMMYY is the date of birth and SSSS is a sequence number. The first digit of the sequence number encodes the century of birth (so that centenarians are distinguished from infants), ...

http://en.wikipedia.org/wiki/Personal_identification_number_%28Denmark%29

Honestly ... 
 

[FysiOlsen]

Comment: I don´t get your point?!

As you can see in my first post (see below) I am well aware of how SSN is written, but my problem was to do the calculation.

"I have a "Cpr nr" text field where the data is entered as: ddmmyy-xxxx (120475-xxxx meaning the person is born April 12th 1975)"