# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save \$200. Learn More
Highlighted
Contributor III

## how to get correct AGE from identity card number

Hi guys,

1)  i m trying to use this script to get the correct year 19xx, instead the wrong year 20xx

=date(date#(left('630502101234',6),'YYMMDD'))

The result is 2/5/2063. my expected result should be 2/5/1963

2) i m trying to use back the same script to get the AGE, but i got a negative age, is -26

=Age(Today(),date#(left('630502101234',6),'YYMMDD'))

Thanks.

Jim

Labels (2)
• ### age formula

1 Solution

Accepted Solutions
Highlighted
Partner

## Re: how to get correct AGE from identity card number

Try this code:

If(left('030502101234',2) > (Year(Today(1)) - 2000)
,MakeDate(1900 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
,MakeDate(2000 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
)

play around with different values

9 Replies
Highlighted
Partner

## Re: how to get correct AGE from identity card number

This is not fool proof, but my take is:
If Left('630502101234',2) > 19 then use 1900s else 2000s.
e.g .
value = 100502101234
2 possible values = 1910 or 2010
if formula returns 1910, person is about 109 years old (not that it is impossible).
If formula returns  2010, person is about age 9

Contributor III

## Re: how to get correct AGE from identity card number

Thanks. But do you have a workable formula?

Highlighted
Partner

## Re: how to get correct AGE from identity card number

Try this code:

If(left('030502101234',2) > (Year(Today(1)) - 2000)
,MakeDate(1900 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
,MakeDate(2000 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
)

play around with different values

Highlighted
Contributor III

## Re: how to get correct AGE from identity card number

Hi,

This part- If(left('030502101234',2), need to add and date() or date#, else it will only return value '03' rite?

Jim

Highlighted
New Contributor III

## Re: how to get correct AGE from identity card number

=date(date#('19' & '' & left('630502101234',6),'YYYYMMDD'))

Highlighted
Contributor III

## Re: how to get correct AGE from identity card number

63 is just 1 if the data I have , how bout is 00, or 11?

Highlighted
Partner

## Re: how to get correct AGE from identity card number

if you try playing around with the formula like below:

left('030502101234',2)
03

Date(left('030502101234',2))
03/01/1900

Date#(left('030502101234',2))
03

having the results in number form makes it easier to troubleshoot and perform maths

2019 - 2000 = 19

2000 + 03 = 2003

Highlighted
Contributor III

## Re: how to get correct AGE from identity card number

Thanks. Let me try it out. Will come back to you.

Highlighted
Contributor III

## Re: how to get correct AGE from identity card number

Hi,

i have tested it, the logic works for me! thank you very much for coming out with this solution.

Thanks!

Rgds,

Jim Chan