Excel Formula Medicare-Régie de l'assurance maladie du Québec (RAMQ)

Excel Formula

A formula I created for a client that you may find useful:

If you have a database or a spreadsheet with:

Last,First,Date of Birth & Gender

You can generate a medicare number without the 2 last digits that are admin codes.

Formula to obtain: SMIS116023XX
A1 Smith
B1 Sam
C1 10/23/2011
D1 Female

Final Formula (in a spreadsheet)
NB:40189 is the google (or Excel) serial number for  January 11, 2010

=IF(C1>=40189,UPPER(LEFT(A1,3)&LEFT(B1,1))&RIGHT(YEAR(C1),2)&IF(D1="Female",MONTH(C1)+50,0&MONTH(C1))&IF(DAY(C1)<10,0&DAY(C1),DAY(C1))&"XX",UPPER(LEFT(A1,3)&LEFT(B1,1))&RIGHT(YEAR(C1),2)&IF(D1="Female",0&MONTH(C1),0&MONTH(C1))&IF(DAY(C1)<10,0&DAY(C1),DAY(C1))&"XX")


The Above Formula will generate a Medicare number up to the last 2 digits
Maybe there is an easierway but this works !

------------------------------------------------------
Régie de l'assurance maladie du Québec (RAMQ)

Information appearing on a Health Insurance Card

1. The bar code, found on the cards issued as of January 11, 2010.
2. The Health Insurance Number, unique to each person, consisting of:
the first three letters of the last name;
the first letter of the first name;
the last two digits of the year of birth;
the month of birth (to which 50 is added to indicate female);
the day of birth;
a two digit administrative code used by the Régie.
3. The person's identity:
first name (if the number of characters in the first name exceeds the space available, only the initial will appear on the card);
last name at birth;
husband's last name, if requested by a woman married before April 2, 1981 or by a woman married outside Québec who exercises her civil rights under that name;
the number of cards issued to the person since 1984.
4. The person's birthdate and sex.
5. The year and month of expiry.
6. The person's photograph and signature, both of which are digitized and incorporated into the card. Cards issued to persons not required to provide a photo and a signature, such as children under age 14, have no photo or signature spaces, while cards issued to persons exempt from providing their photo, their signature or both, are marked "exempté" in the appropriate space(s). (From Wikpedia)

http://en.wikipedia.org/wiki/R%C3%A9gie_de_l%27Assurance_Maladie_du_Qu%C3%A9bec