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 – CNP-uri de dupa 2000

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