Cum extragi data nasterii, varsta, sexul dintr-un CNP

Intrebare: cum pot sa extrag dintr-un CNP data nasterii, varsta si sexul folosind Excel? Daca se poate as prefera sa nu le scriu manual in tabelele la care lucrez.

Foarte buna intrebarea, luam ca si exemplu tabelul urmator:

Extrage data nasterii, varsta, sexul CNP 2

Coloanele varsta, data nasterii si sex am vrea sa se calculeze automat in functie de CNP.

Structura informatiilor din CNP

Probabil ca majoritatea stiti ca fiecare CNP are urmatoarea structura:

SAALLZZXXXXXX

  • S – Sex (1 Masculin, 2 Feminin)
  • AA – Anul nasterii din 2 cifre
  • LL – Luna nasterii din 2 cifre
  • ZZ – Ziua nasterii din 2 cifre
  • X – alte cifre care nu ne intereseaza in momentul de fata

Rezolvarea problemei

Un lucru important la orice lucru pe care vrem sa il facem in Excel e sa ne gandim intai cum mai exact vom rezolva problema inainte sa incepem sa scriem formule:

  • Ca sa gasim sexul: extragem primul caracter din CNP, daca este 1 afisam M, daca este 2 afisam F
  • Ca sa gasim data nasterii: extragem urmatoarele 6 caractere din CNP si le punem intr-un camp de tip data
  • Ca sa gasim varsta: varsta e de fapt diferenta dintre data de azi si data nasterii

Asta inseamna ca probabil vom avea nevoie de cateva formule care sa ne permita sa:

  • Extragem unul sau mai multe caractere din coloana CNP (LEFT, MID)
  • Afisam M sau F in functie de valoarea 1 sau 2 (IF)
  • Stim data de astazi (TODAY)
  • Sa transformam 3 numere intr-o data (DATE)
  • Scadem 2 date pentru a vedea varsta (DATEDIF)

Formule

Vorbind si mai concret, mai jos aveti formulele pe care le-am folosit, A2 reprezinta prima inregistrare din coloana CNP.

  • Ca sa gasim sexul: =IF(LEFT(A2,1)=”1″,”M”,”F”)
  • Ca sa gasim data nasterii: =DATE(MID(A2,2,2),MID(A2,4,2),MID(A2,6,2))
  • Ca sa gasim varsta: =DATEDIF(DATE(MID(A2,2,2),MID(A2,4,2),MID(A2,6,2)),TODAY(),”y”)

Descarca exemplu

Cu un click aici poti descarca fisierul excel pe care l-am folosit ca si exemplu.

PS: Mersi Raul pentru ideea de articol si rezolvare.


Update

Modelul de mai sus functiona foarte bine pentru CNP-uri din secolul 20 (intre 1900 si 1999). Din pacate pentru cei dintre noi nascuti dupa 2000 sau inainte de 1900, dadea erori de calcul.

Am refacut modelul, il puteti gasi aici.

Daca va intereseaza formulele, vedeti mai jos:

Data nasterii

=DATE(IF(OR(MID(A2,1,1)="1",MID(A2,1,1)="2"),"19"&MID(A2,2,2),IF(OR(MID(A2,1,1)="3",MID(A2,1,1)="4"),"18" & MID(A2,2,2),"20"&MID(A2,2,2))),MID(A2,4,2),MID(A2,6,2))

Varsta

=ROUNDDOWN((TODAY()-C2)/365,0)

Sex

=IF(OR(LEFT(A2,1)="1",LEFT(A2,1)="3",LEFT(A2,1)="5",LEFT(A2,1)="7"),"M","F")


  • Pingback: 6 luni si 50 de articole despre Excel | Excel Ninja()

  • Bulgarescu Doru

    Interesant si de foarte mare ajutor.Multumesc !

  • cristina

    aici DATEDIF(DATE(MID(A2,2,2),MID(A2,4,2),MID(A2,6,2)),TODAY(),”y”) formual e cu virgula “,” si in fisier e cu “;”.
    Valida e cea cu “;”

    • Amandoua pot fi valide in functie de setarile de limba la calculator

  • pierdutinspatiu

    Daca persoana este nascuta dupa anul 2000 ? ce se intampla?
    CNP Varsta Data nasterii Sex
    2111010337673 102 10.10.1911 F

    cum pot rezolva aceasta situatie… ?

  • Anonymous

    Formula ce tine cont si de prefixul anului 19 sau 20 este:
    =DATEDIF(DATE(CONCATENATE(IF((MID(B709;1;1)=”5″)+(MID(B709;1;1)=”6″);”20″;”19″);MID(B709;2;2));MID(B709;4;2);MID(B709;6;2));TODAY();”y”)

  • Geo

    Buna seara, am nevoie de un mic ajutor.
    Vreau sa numar cati barbati si cate femei sunt intr-un fisier excel avand la dispozitie CNP-uri
    Multumesc

    • Buna, poti proceda in felul urmator: extragi primul caracter din cnp folosind formula LEFT, dupa ce ai extras pentru toate CNP-urile folosesti un COUNTIF pentru a numara cati barbati si cate femei sunt. Vezi mai multe despre aceste formule mai jos:
      http://excelninja.ro/functii-left-right-search-len-concatenate/
      http://excelninja.ro/formule-countif-sumif/

      Sper ca am fost de folos.

      • Anonymous

        am incercat si eu cu LEFT + COUNTIF dar nu-mi iese …gresesc undeva ….

        • Da-mi mai multe informatii, scriem-mi pe pagina de contact
          http://excelninja.ro/contact/

          • g

            am cnp-uri si vreau sa stiu cati barbati si cate femei sunt intr-o anumita comunitate, pur si simplu numarul de femei/barbati. cred ca folosesc o formula gresit si cad in aceeasi greseala

  • Geo

    multumesc

  • Oana

    Am incercat sa extrag data din cnp dar nu a mers formula cred ca e din cauza ca anul e format din 2 cifre si nu din 4…

  • Nicolae Borota

    Codul CNP poate incepe si cu alte cifre.
    Vezi: http://ro.wikipedia.org/wiki/Cod_numeric_personal sau http://www.valideaza.ro/valideaza-cnp.php
    [S][AA][LL][ZZ][CJ][XXX][C]

    [S] – Cod sex, masculin/feminin, se aloca astfel:
    1/2 – nascuti intre 1 ian 1900 si 31 dec 1999;
    3/4 – nascuti intre 1 ian 1800 si 31 dec 1899;
    5/6 – nascuti intre 1 ian 2000 si 31 dec 2099;
    7/8 – pentru rezidenti;
    9/9 – pentru persoanele cu cetatenie straina.
    [AA] – An nastere
    [LL] – Luna nastere
    [ZZ] – Zi nastere
    [CJ] – Cod judet
    [XXX] – Numar de ordine
    [C] – Cifra de control

  • Multumesc de observatii, am actualizat articolul in urma cu ceva vreme

  • Valentin Nicolae

    Pentru masculin/feminin se poate folosi functia EVEN sau ODD. In acest mod nu mai suntem restrictionati de 1,2,3 etc.
    =IF(ISEVEN(LEFT(C11,1)),”F”,”M”)
    Apoi se poate folosi functia COUNTIFS
    =COUNTIFS($K:$K,”F”)
    Pana aici toate si frumoase, dar….exista un dar, poate fi folosita o singura formula care sa returneze direct numarul de femei (de exemplu) dintr-o anumita zona?

    • Se poate, trebuie doar sa avem toate CNP-urile dintr-o anumita zona. Daca avem CNP-urile putem proceda ca si in acest articol:
      http://excelninja.ro/numar-barbati-si-femei-din-cnp/

      • Valentin Nicolae

        Am inteles aceasta modalitate. Eu intrebam daca poate fi numarat direct, fara formule intermediare. Doar o singura formula

        • Cred ca avem nevoie de cel putin 2 formule, nu stiu sa putem vedea numarul cu o singura formula

    • Iulian

      Foarte tare solutia. Eu preferam sa utilizez MOD ca si formula
      =IF(MOD(LEFT(A2,1),2),”M”,”F”)

  • Catalina

    Cum determin NUMARUL de persoane de sex feminin si nr de persoane de
    sex masculin(am deja extrase intr-o coloana pe sexe) si sa inserez un
    grafic in alta pagina de lucru care sa redea structura pe sexe.

  • Ciprian Cercel

    in tabelul tau persoana nascuta in 2003 are varsta de 112 ani

    • Tabelul l-am primit si eu de la cineva, vad ca nu e adaptat pentru cei nascuti dupa 2003, o sa incerc sa il revizuiesc. Mersi de comment 🙂

  • Ramona Mihaela Popa

    Esti grozav, multumesc de ajutor!

  • am marea rugaminte sa ma ajutati cu determinarea virstei din CNP pentru ca forumula nu lucreaza corect decit cu CNP uri care incep cu 5 si 6. Am rezolvat problema pentru CNP urile cu 1 si 2 introducind -100 la sfirsitul formulei, dar ce ma fac atunci cind intr-o clasa de elevi am CNPuri amestecate? Ar trebui sa existe o solutie, multumesc oricum pentru formula, m-a ajutat sa construiesc o fisa de inscriere elevi pentru gimnaziu.

    A doua intrebare se refera la o formula care sa-mi permita sa stabilesc virsta implinita a elevilor pina la 31 decembrie 2015, am reusit eu cumva dar artificiul facut e cumva ciudat, obtin date corecte daca schimb data calculatorului la 31 dec 🙂 Fisierul este aici

    http://www.didactic.ro/cautare?search=+Fisa+de+inscriere+elevi+an+scolar+format+electronic+tipizat+excel&x=10&y=11

    • Buna, multumesc pentru mesaj

      Modelul in Excel functioneaza ok pentru CNP-uri dinainte de 2000, dar de dupa 2000, da erori. Urmatorul articol va fi un model care rezolva aceasta problema.

  • Pingback: Cadouri de ziua meaexcelninja.ro | excelninja.ro()

  • Pingback: Ce a mers in 2015 | excelninja.ro()

  • Oana

    Buna ziua!
    Cum pot afla varsta la o anumita data, alta decat Today, functie de data nasterii?

    • Salut Oana

      Varsta o putem afla prin diferenta dintre 2 date. Daca nu vrem sa folosim Today(), putem alege o alta data ca si referinta.

      • Oana

        Multumesc, insa nu stiu cum ar trebui sa fie scrisa formula in acest caz. Imi puteti da un exemplu, va rog?

        • Da

          In celula A1 scriu data nasterii: 1993-01-12; in celula A2 scriu data de referinta: 2011-07-04.

          Si acum formula: =A2-A1
          Rezultatul mi-l da in zile, daca vreau saptamani impart la 7, daca vreau ani la 365, etc.

      • Oana

        Va multumesc!