View original (French)

How to extract date of birth from the NISS in Excel

Ziomzbelgii -  
tontong Posted messages 2575 Registration date   Status Member Last intervention   -
Hello,

I'm unable to solve the following problem:

I need to have a list of birth dates of the employees working for our company. In Excel, I have all the national registry numbers. There are 700 of them, and unfortunately, I only have the NISS and I need to extract the birth dates.

Is it possible to do that?

I thank in advance the person who can help me (if there will be one, of course)

Szymon

Configuration: Windows 7 / Firefox 17.0

2 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    NISS ????

    Michael
    0
    1. Ziomzbelgii
       
      NISS = National Registration Number (11 digits)
      In short, it's 11 digits and in the first 6 digits, there is the date of birth in reverse.
      Ex: 650929 554 25 = the first 6 digits indicate that the person was born on 29 / 05 / 1965.
      0
    2. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Hello,
      Just out of personal curiosity but also because of Excel's hesitations:
      how do we distinguish the numbers from 2010 from the numbers from 1910?
      0
  2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello

    Thank you for the clarification because I was about to go with the French INSEE code

    A custom function after setting the extraction column to date format 

    Function dater_naissance(niss As String) As Date niss = Left(niss, 6) dater_naissance = CDate(Right(niss, 2) & "/" & Mid(niss, 3, 2) & "/" & Left(niss, 2)) End Function


    But we can do it with a formula if you are resistant to VBA

    Michel
    0