Un des participants à l’une de mes formations m’a posé une question « Comment extraire la date de naissance du numéro de registre national avec Excel ?« .
Cette question m’a donné l’idée de cet article.
Le contexte
Le numéro de registre national ou numéro d’identification du registre national ou numéro national (N.N.) en Belgique est un identifiant unique et personnel composé de 11 chiffres.
- Les 6 premières compositions forment la date de naissance inversée (AA/MM/JJ)
- Les 3 positions suivantes constituent le compteur journalier des naissances. Il existe un compteur journalier par genre, ce nombre étant pair pour les femmes et impair pour les hommes
- Les 2 dernières positions constituent le chiffre de contrôle (Check Digit). Ce chiffre de contrôle est une suite de 2 chiffres formant un nombre compris entre 01 et 97. Ce nombre est égal à 97 moins le reste de la division par 97 du nombre formé (complément à 97 du reste de la division par 97
Il existe des cas particuliers comme par exemple la date de naissance inconnue. Ces cas ne sont pas traités dans ce billet. Pour en savoir plus, je vous renvoie au document officiel du gouvernement belge
Comment extraire la date de naissance du numéro national ?
Petit rappel
Une date dans Excel est un nombre et seul le format Date permet de l’interpréter comme une date.
Ainsi si nous tapons le nombre 43256 dans une cellule et qu’ensuite nous lui appliquons le format Date (JJ/MM/AA), nous obtiendrons le 05/06/2018, date de publication de cet article. Si nous tapons le chiffre 1 dans une cellule contenant une date, nous verrons le 01/01/1900 ou 01/01/1904 si nous avons un Mac.
En résumé, une date est un numéro de série formaté.
La fonction DATE
Syntaxe : DATE(année;mois;jour)
La fonction DATE renvoie le numéro de série d’une date en fonction des valeurs passées aux arguments Année, Mois et Jour et applique automatiquement le format Date
Exemple qui renverra le 05/06/2018
= DATE(2018;6;5)
Les fonctions utilisées dans la formule
La fonction DATE ainsi que les fonctions d’extractions GAUCHE/LEFT, STXT/MID, ANNEE/YEAR
Pour l’exemple, nous prendrons un numéro national avec comme numéro de contrôle (les deux derniers chiffres) 00 soit 81.04.03-101.00
Une petite difficulté s’est ajoutée depuis l’année 2000. En effet, il est impossible à la seule lecture du numéro national de distinguer les personnes nées à partir de 2000 des personnes nées au début du siècle dernier sans effectuer un calcul sur le chiffre de contrôle (lire le chapitre Le numéro d’identification du Registre national des personnes physiques et le passage à l’an 2000).
Par facilité et comme la question était posée dans le cadre du monde du travail, nous considérerons que les personnes dont les deux premiers chiffres de gauche du numéro national ont une valeur inférieure ou égale aux deux premiers chiffres de l’année en cours sont nés au 21ème siècle.
=DATE((19 + (GAUCHE(A2;2)<=DROITE(ANNEE(AUJOURDHUI());2))) * 100 + GAUCHE(A2;2);STXT(A2;4;2);STXT(A2;7;2))
Comment connaître le sexe de la personne d’après son numéro national ?
Comme expliqué plus haut, si le nombre de 3 chiffres qui suit immédiatement la date de naissance est pair, il s’agit d’une personne de sexe féminin et masculin si celui ci est impair.
Formule
Les fonctions utilisées sont CHOISIR/CHOOSE, EST.PAIR/ISEVEN et STXT/MID
=CHOISIR(EST.PAIR(STXT(A2;10;3))+1;"M";"F")
Commentaires récents