Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to calculate age from Malaysia IC number based on the year of birth instead of birthday date of IC malaysia.

4,591 views
Skip to first unread message

Khor Kang Xian

unread,
Sep 7, 2022, 4:41:44 AM9/7/22
to
Hi all, i would like to ask how to calculate age based on the year of birth, instead of birthday date， which extract from Malaysia IC number.

For example :
ic number: 161214-07-0109, the age follow birthday the result is "5" .
if I want to follow the year of birth supposedly is "6 ".

my current formula is stated as below:
=DATEDIF(DATE(IF(LEFT($F16)="2","20",IF(LEFT($F16)="1","20",IF(LEFT($F16)="0","20","19")))&LEFT($F16,2),MID($F16,3,2),MID($F16,5,2)),TODAY(),"Y")

Kindly give me the advice , really appreciate . thanks in advance

Claus Busch

unread,
Sep 7, 2022, 5:15:34 AM9/7/22
to
Hi,

Am Wed, 7 Sep 2022 01:41:41 -0700 (PDT) schrieb Khor Kang Xian:

> Hi all, i would like to ask how to calculate age based on the year of birth, instead of birthday date? which extract from Malaysia IC number.
>
> For example :
> ic number: 161214-07-0109, the age follow birthday the result is "5" .
> if I want to follow the year of birth supposedly is "6 ".
>
> my current formula is stated as below:
> =DATEDIF(DATE(IF(LEFT($F16)="2","20",IF(LEFT($F16)="1","20",IF(LEFT($F16)="0","20","19")))&LEFT($F16,2),MID($F16,3,2),MID($F16,5,2)),TODAY(),"Y")

try:
=DATEDIF(DATEVALUE(MID(F16,5,2)&"/"&MID(F16,3,2)&"/"&IF(LEFT(F16,2)>=TEXT(TODAY(),"yy"),19,20)&LEFT(F16,2)),TODAY(),"y")


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Khor Kang Xian

unread,
Sep 8, 2022, 12:02:25 AM9/8/22
to
Dear Claus B.

thanks for your reply, but it does not work in my google sheet , it come out with error.

Error
DATEVALUE parameter '14/12/2016' cannot be parsed to date/time.

just for your information, the identity card number in malaysia is YYMMDD- XX-XXX for example 161214-08-0202, mean born in 14 dec 2016 .

regards,

Kang Xian

Alex Plantema

unread,
Sep 8, 2022, 4:00:10 AM9/8/22
to
Op do 08-09-2022 om 06:02 schreef Khor Kang Xian:

> DATEVALUE parameter '14/12/2016' cannot be parsed to date/time.
>
> just for your information, the identity card number in malaysia is YYMMDD- XX-XXX for example 161214-08-0202, mean born in 14 dec 2016 .

Try =MOD(DATEDIF(DATEVALUE(MID(F16,5,2)&"-"&MID(F16,3,2)&"-"&19&LEFT(F16,2)),TODAY(),"y");100)

--
Alex.

Alex Plantema

unread,
Sep 8, 2022, 4:01:48 AM9/8/22
to
Op do 08-09-2022 om 06:02 schreef Khor Kang Xian:

> DATEVALUE parameter '14/12/2016' cannot be parsed to date/time.
>
> just for your information, the identity card number in malaysia is YYMMDD- XX-XXX for example 161214-08-0202, mean born in 14 dec 2016 .

Try =MOD(DATEDIF(DATEVALUE(MID(F16,5,2)&"-"&MID(F16,3,2)&"-"&19&LEFT(F16,2)),TODAY(),"y"),100)

--
Alex.

Aim Digital

unread,
Sep 22, 2022, 4:14:14 AM9/22/22
to
Try This formula

=YEAR(TODAY()) - (@IFS(LEFT(F16,1)="0", CONCAT("20", LEFT(F16, 2)), LEFT(F16,1) <> "0", CONCAT("19", LEFT(F16,2))))

Lukas Radzevičius

unread,
Feb 3, 2023, 4:41:42 AM2/3/23
to
This works great, thank you

For me I needed a bit of modification
so I added MID function for 6 numbers for date like this (MID(B2:$B;2;6) 960505 YY MM DD form code like 496050500000

AND added arrayformula for use for whole range of data

=ArrayFormula(IFERROR(MOD(DATEDIF(DATEVALUE(MID(MID(B2:$B;2;6);5;2)&"-"&MID(MID(B2:$B;2;6);3;2)&"-"&19&LEFT(MID(B2:$B;2;6);2));TODAY();"y");100)))

(If you copy check if your region is same ( about ; and , separators))

0 new messages