1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • How to calculate age from Malaysia IC number based on the year of birth

    From Khor Kang Xian@21:1/5 to All on Wed Sep 7 01:41:41 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Sep 7 11:15:28 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Khor Kang Xian@21:1/5 to claus...@t-online.de on Wed Sep 7 21:02:22 2022
    On Wednesday, September 7, 2022 at 5:15:34 PM UTC+8, claus...@t-online.de wrote:
    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


    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Alex Plantema@21:1/5 to All on Thu Sep 8 10:01:44 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Alex Plantema@21:1/5 to All on Thu Sep 8 10:00:04 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Aim Digital@21:1/5 to Alex Plantema on Thu Sep 22 01:14:12 2022
    On Thursday, September 8, 2022 at 4:01:48 PM UTC+8, Alex Plantema wrote:
    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)

    --
    Ale

    Try This formula

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Lukas_Radzevi=C4=8Dius?=@21:1/5 to Alex Plantema on Fri Feb 3 01:41:39 2023
    On Thursday, 8 September 2022 at 11:01:48 UTC+3, Alex Plantema wrote:
    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.


    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))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)