NIC to DOB Excel Formula — Sri Lanka NIC Decode in Excel & Google Sheets

By Pubudu Tharanga · Last updated August 2026 · Not affiliated with DRP

Need to decode hundreds of Sri Lankan NIC numbers in a spreadsheet? These copy-paste formulas work in Microsoft Excel and Google Sheets for both the old 9-digit (+ V/X) and new 12-digit NIC formats. Each formula is leap-year safe.

If you only need to decode one NIC at a time, use the online NIC to DOB tool instead — it's faster and handles edge cases automatically.

1. Detect NIC Format (Old or New)

Use this helper first — it tells you whether a cell is old (10 chars) or new (12 digits):

=IF(LEN(A2)=10,"Old",IF(LEN(A2)=12,"New","Invalid"))

2. Extract Date of Birth

Old NIC (9-digit + V/X) — Date of Birth

=DATE(
  1900 + VALUE(LEFT(A2, 2)),
  1,
  IF(VALUE(MID(A2, 3, 3)) > 500,
     VALUE(MID(A2, 3, 3)) - 500,
     VALUE(MID(A2, 3, 3)))
)

Format the result cell as Date (e.g. dd/mm/yyyy). This uses January 1 of the birth year as the base and adds the day-of-year offset.

New NIC (12-digit) — Date of Birth

=DATE(
  VALUE(LEFT(A2, 4)),
  1,
  IF(VALUE(MID(A2, 5, 3)) > 500,
     VALUE(MID(A2, 5, 3)) - 500,
     VALUE(MID(A2, 5, 3)))
)

Universal formula (works for both old and new)

=IF(LEN(A2)=10,
  DATE(1900+VALUE(LEFT(A2,2)), 1, IF(VALUE(MID(A2,3,3))>500, VALUE(MID(A2,3,3))-500, VALUE(MID(A2,3,3)))),
  IF(LEN(A2)=12,
    DATE(VALUE(LEFT(A2,4)), 1, IF(VALUE(MID(A2,5,3))>500, VALUE(MID(A2,5,3))-500, VALUE(MID(A2,5,3)))),
    "Invalid NIC")
)

3. Extract Gender

If the day-of-year code is > 500, the person is female.

Old NIC — Gender

=IF(VALUE(MID(A2, 3, 3)) > 500, "Female", "Male")

New NIC — Gender

=IF(VALUE(MID(A2, 5, 3)) > 500, "Female", "Male")

Universal — Gender

=IF(LEN(A2)=10, IF(VALUE(MID(A2,3,3))>500,"Female","Male"),
 IF(LEN(A2)=12, IF(VALUE(MID(A2,5,3))>500,"Female","Male"), "Invalid"))

4. Calculate Age from NIC

Chain the DOB formula with DATEDIF. Assumes DOB is in B2 (use the universal DOB formula above in B2):

=DATEDIF(B2, TODAY(), "Y")

Or combine everything into one cell:

=DATEDIF(
  IF(LEN(A2)=10,
    DATE(1900+VALUE(LEFT(A2,2)), 1, IF(VALUE(MID(A2,3,3))>500, VALUE(MID(A2,3,3))-500, VALUE(MID(A2,3,3)))),
    DATE(VALUE(LEFT(A2,4)), 1, IF(VALUE(MID(A2,5,3))>500, VALUE(MID(A2,5,3))-500, VALUE(MID(A2,5,3))))
  ),
  TODAY(), "Y"
)

5. Voter Status (Old NIC Only)

=IF(LEN(A2)=10, IF(UPPER(RIGHT(A2,1))="V","Voter","Non-voter"), "N/A (new format)")

6. Convert Old NIC to New NIC

="19"&LEFT(A2,2)&MID(A2,3,3)&"0"&MID(A2,6,4)

This gives the 11-digit body; the 12th check digit must come from the official DRP record.

7. Full Summary Table

8. Google Sheets Notes

All formulas above work identically in Google Sheets. One difference: Sheets uses commas as separators in most locales; if your Sheets uses semicolons, replace all , with ;.

Google Sheets tip: use ARRAYFORMULA to apply to an entire column at once:

=ARRAYFORMULA(IF(LEN(A2:A)=10,
  DATE(1900+VALUE(LEFT(A2:A,2)),1,IF(VALUE(MID(A2:A,3,3))>500,VALUE(MID(A2:A,3,3))-500,VALUE(MID(A2:A,3,3)))),
  IF(LEN(A2:A)=12,
    DATE(VALUE(LEFT(A2:A,4)),1,IF(VALUE(MID(A2:A,5,3))>500,VALUE(MID(A2:A,5,3))-500,VALUE(MID(A2:A,5,3)))),
  "")))