Malaysian IC Number Format:
The IC number consists of 12 digits in the following pattern: YYMMDD-PB-###G
First 6 digits (YYMMDD):
Represent the birth date in YY MM DD format.
Example: If someone was born on 18 March 1995, it would appear as 950318.
Next 2 digits (PB):
Indicate the place of birth.
For Malaysians, this is usually a state code (e.g., 01 for Johor, 14 for Kuala Lumpur).
Foreign-born persons have different codes (e.g., 71 for Singapore, 72 for Sarawak-born before Malaysia's formation).
Next 3 digits (###):
A serial number (randomly assigned for people born on the same day in the same location).
Last digit (G):
Represents gender:
Odd number (1,3,5,7,9) → Male
Even number (0,2,4,6,8) → Female
Excel Formula to Convert IC Format into Date Format
Here is how to convert the date of birth from the IC format into date format in Excel.
Enter the IC number into cell A1. In cell B1, enter the following code.
=TEXT(DATE(IF(LEFT(A1,2)<=RIGHT(YEAR(TODAY()),2), 2000, 1900) + LEFT(A1,2), MID(A1,3,2), MID(A1,5,2)), "dd-mm-yyyy")
Example: If A1 = 770715-14-5432, then B1 will display: 15-07-1977
The code assumes that the IC format is always correct.