NIC Calculator Excel Formula

NIC Calculator Excel Formula (NIC number converter MS Excel formula) can help to extracting key details such as DoB (Date of Birth), gender and age from the Identity Card number.

The National Identity Card (NIC) in Sri Lanka holds significant importance in various aspects of a citizen’s life. It is not only an essential document for identification but also contains information encoded within the NIC number itself. The NIC number is structured in a way that allows easy access to vital personal details, including the individual’s date of birth and gender. This makes it especially convenient for performing various calculations, such as determining age.

In this document, we will guide you through how to extract these essential details—such as date of birth (DoB) and gender—from the Sri Lankan NIC number using Microsoft Excel. Whether you’re working on a project that requires NIC-related calculations or need to process multiple NIC numbers efficiently, the methods we’ll explain will prove to be useful.

These formulas can convert both old and new NIC numbers. You have to create same formulas in your spreadsheet and make sure to format the cells (CTRL+1) as “Number”.

Spreadsheet

You can visit Google Sheet and see the output in live. Also, you can download this NIC Calculator Excel Formula sheet into your system.

Formula : NIC Calculator Excel Formula / NIC Convertor in MS Excel

Formula B2

=IF(AND(LEN(A2)=12,OR(LEFT(A2,2)="19",LEFT(A2,2)="20")),LEFT(A2,4),IF(AND(LEN(A2)=10,ISNUMBER(VALUE(LEFT(A2,9)))),LEFT(A2,2)+1900,"Invalid"))

1. First IF statement:
2. IF(AND(LEN(A2)=12, OR(LEFT(A2,2)=”19″, LEFT(A2,2)=”20″)), LEFT(A2,4), … )
   • LEN(A2)=12 checks if the length of the value in cell A2 is 12 characters.
   • OR(LEFT(A2,2)=”19″, LEFT(A2,2)=”20″) checks if the first two characters in A2 are either “19” or “20”.
   • If both of these conditions are TRUE (i.e., the value is a 12-character string starting with “19” or “20”), then LEFT(A2,4) returns the first 4 characters of A2 (which would be the year, like “1990” or “2005”).
   • If these conditions aren’t met, the formula moves to the next IF statement.
3. Second IF statement (nested inside the first one):
4. IF(AND(LEN(A2)=10, ISNUMBER(VALUE(LEFT(A2,9)))), LEFT(A2,2)+1900, “Invalid”)
   • LEN(A2)=10 checks if the length of the value in A2 is 10 characters.
   • ISNUMBER(VALUE(LEFT(A2,9))) checks if the first 9 characters of A2 (using LEFT(A2,9)) can be converted to a number. If it can, this condition is TRUE.
   • If both conditions are TRUE (i.e., the string is 10 characters long and the first 9 characters are numeric), then LEFT(A2,2)+1900 is executed. It takes the first 2 characters of A2, interprets them as a number (the year), and adds 1900 to it. For example, if A2 starts with “90”, it returns “1990”.
   • If the conditions in this second IF statement aren’t met, it returns “Invalid”.

Summary of Logic:

• If the value in A2 is a 12-character string starting with “19” or “20”, the formula returns the first 4 characters (the year).
• If the value in A2 is a 10-character string, and the first 9 characters are numeric, it assumes the value is a 2-digit year and returns that year plus 1900.
• If neither condition is met, it returns “Invalid”.

Formula C2

=IF(AND(LEN(A2)=12,OR(LEFT(A2,2)="19",LEFT(A2,2)="20")),MONTH(IF(VALUE(MID(A2,5,3))>500,VALUE(MID(A2,5,3))-500,VALUE(MID(A2,5,3)))),IF(AND(LEN(A2)=10,ISNUMBER(VALUE(LEFT(A2,9)))),MONTH(IF(VALUE(MID(A2,3,3))>500,VALUE(MID(A2,3,3))-500,VALUE(MID(A2,3,3)))),"Invalid"))

• Refer above for character check IF statement under Formula B2:
• MID(A2,5,3): This extracts 3 characters starting from the 5th position in A2. This is presumably the “month” encoded in the string, but it might be in a special format.
  • VALUE(MID(A2,5,3)): Converts the extracted 3 characters into a number.
  • IF(VALUE(MID(A2,5,3))>500, VALUE(MID(A2,5,3))-500, VALUE(MID(A2,5,3))):
  • If the extracted number is greater than 500, it subtracts 500 from it. This could be because the values are encoded with a certain offset (like 501 for January, etc.).
  • If the number is not greater than 500, it simply uses the value as it is (likely corresponding to the month in the range 1-12).
  • MONTH(…): The result is passed to the MONTH() function, which converts the number into a month value (1-12).
• Second IF statement (nested inside the first one) as same as above:

Summary of Logic:

• If A2 is a 12-character string starting with “19” or “20”, it extracts a “month” from the 5th to the 7th characters (adjusting if the number is greater than 500) and returns the month (1-12).
• If A2 is a 10-character string with a numeric value in the first 9 characters, it extracts the “month” from the 3rd to the 5th characters and returns the month (1-12).

Formula D2

=IF(AND(LEN(A2)=12,OR(LEFT(A2,2)="19",LEFT(A2,2)="20")),DAY(IF(VALUE(MID(A2,5,3))>500,VALUE(MID(A2,5,3))-500,VALUE(MID(A2,5,3)))),IF(AND(LEN(A2)=10,ISNUMBER(VALUE(LEFT(A2,9)))),DAY(IF(VALUE(MID(A2,3,3))>500,VALUE(MID(A2,3,3))-500,VALUE(MID(A2,3,3)))),"Invalid"))

• Refer above for character check IF statement under Formula B2:
• MID(A2,5,3): Extracts 3 characters starting from the 5th position of A2. This part is likely representing the “day” encoded in the string (in an offset format).
  • VALUE(MID(A2,5,3)): Converts the extracted 3-character string into a number.
  • IF(VALUE(MID(A2,5,3))>500, VALUE(MID(A2,5,3))-500, VALUE(MID(A2,5,3))):
  • If the extracted value is greater than 500, it subtracts 500 from it (possibly to reverse an encoding scheme).
  • Otherwise, it uses the value as it is.
  • DAY(…): Converts the result of the inner calculation (the day) into a day of the month. The result should be between 1 and 31.
• Second IF statement (nested inside the first one) as same as above:

Summary of Logic:

• If A2 is a 12-character string starting with “19” or “20”, it extracts a 3-character string starting from the 5th position (presumably the day) and converts it into a valid day of the month, adjusting if necessary (by subtracting 500 if the value is over 500).
• If A2 is a 10-character string with numeric data in the first 9 characters, it extracts the day from characters 3 to 5 and adjusts it the same way.

Formula E2

=IF(AND(LEN(A2)=12,OR(LEFT(A2,2)="19",LEFT(A2,2)="20")),IF(VALUE(MID(A2,5,3))<500,"Male","Female"),IF(AND(LEN(A2)=10,ISNUMBER(VALUE(LEFT(A2,9)))),IF(VALUE(MID(A2,3,3))<500,"Male","Female"),"Invalid"))

• Refer above for character check IF statement under Formula B2:
• IF(VALUE(MID(A2,5,3))<500, “Male”, “Female”),
  • MID(A2,5,3): This extracts a 3-character substring starting at the 5th position of the string in A2 (this is likely a code used to determine gender, based on the given formula).
  • VALUE(MID(A2,5,3)): This converts the extracted 3-character substring into a number.
  • IF(VALUE(MID(A2,5,3))<500, “Male”, “Female”):
    • If the numeric value is less than 500, it returns “Male”.
    • If the numeric value is 500 or greater, it returns “Female”.
• Second IF statement (nested inside the first one) as same as above:

Summary of Logic:

• If A2 is a 12-character string starting with “19” or “20”:
  • The formula extracts the 5th to 7th characters (representing a value) and checks if it’s less than 500.
  • If it’s less than 500, the result is “Male”, otherwise, it is “Female”.
• If A2 is a 10-character string starting with numeric data:
  • The formula extracts the 3rd to 5th characters (representing a value) and checks if it’s less than 500.
  • If it’s less than 500, the result is “Male”, otherwise, it is “Female”.

Formula F2

=DATEDIF(DATE(B2,C2,D2), TODAY(), "Y")

• DATE(B2, C2, D2): This creates a date from the year in cell B2, the month in cell C2, and the day in cell D2.
• TODAY(): This function returns the current date.
• DATEDIF(start_date, end_date, “Y”): This function calculates the difference between two dates, in this case, the date in B2, C2, and D2 and today’s date, with the “Y” argument indicating the difference in years (i.e., the person’s age).

Summary of Logic:

• If B2 = 1990, C2 = 5, and D2 = 15 (May 15, 1990), the formula would calculate the person’s age as of today’s date.