xDataIDAbout
EN, choose language

Read hundreds of NIKs at once, or use the Excel formulas

Paste a column of NIKs from a spreadsheet, or any text containing them. Every 16-digit number inside is read into one table: region, gender, birth date, age, and a note when something looks off. Prefer to work in Excel itself? The formulas are below.

Everything is processed in your browser. The list never leaves this device, so it is safe for employee or member data.

Results

Nothing read yet. Paste a list of NIKs above, then press Read all.

NIKs in Excel: formulas for birth date, age, and gender

Updated 13 October 2026

Have a column of Indonesian NIKs in Excel or Google Sheets? Two things must be right before any formula works: the NIK has to be stored intact, and each segment has to be cut at the right position. What each segment means is explained on the NIK decoder page.

How to stop Excel turning a NIK into 1.27E+15

Excel stores numbers to 15 significant digits. A NIK has 16, so the moment it is typed or pasted as a number the last digit becomes 0 and it displays as 1.27118E+15. The data is permanently altered, not just displayed differently, and changing the cell format afterwards does not bring the lost digit back. Three ways to keep it intact:

  1. Format the column as Text before typing. Select the column, right-click, Format Cells, then Text.
  2. Start with an apostrophe, as in '1271182308840005. The apostrophe is not displayed.
  3. When pasting from elsewhere, use Paste Special, then Text, or import the CSV with the NIK column set to Text.

Google Sheets has the same limit. If the NIKs in your file already end in 0, fetch them again from the original source; no formula can recover the lost digit.

Excel formulas: birth date, age, and gender from a NIK

The NIK is in cell A2, stored as text. The day is at position 7 for 2 digits, the month at 9, the year at 11. A day above 40 marks a female holder; subtract 40 for the real day.

ResultFormula
Gender=IF(VALUE(MID(A2,7,2))>40,"F","M")
Day (1–31)=IF(VALUE(MID(A2,7,2))>40,VALUE(MID(A2,7,2))-40,VALUE(MID(A2,7,2)))
Month=VALUE(MID(A2,9,2))
Year (century guessed)=IF(VALUE(MID(A2,11,2))<=VALUE(RIGHT(YEAR(TODAY()),2)),2000,1900)+VALUE(MID(A2,11,2))
Full birth date=DATE(IF(VALUE(MID(A2,11,2))<=VALUE(RIGHT(YEAR(TODAY()),2)),2000,1900)+VALUE(MID(A2,11,2)),VALUE(MID(A2,9,2)),IF(VALUE(MID(A2,7,2))>40,VALUE(MID(A2,7,2))-40,VALUE(MID(A2,7,2))))
Age in years, birth date in B2=DATEDIF(B2,TODAY(),"Y")
6-digit region code=LEFT(A2,6)

The formulas use commas as argument separators. If your Excel uses semicolons, replace every comma with a semicolon. The two-digit year is guessed the same way as the tool on this page: years not yet past are read as 20xx, the rest as 19xx. To turn region codes into names, download the code list on the region codes page and use VLOOKUP.

If you have hundreds of rows and would rather skip formulas, paste the NIK column into the box at the top. The result table can be copied as TSV and pasted next to your NIK column.

How it works and its limits

  • Only runs of exactly 16 digits are read. Digits attached to other digits, such as a 17-digit account number, are skipped.
  • Duplicates are shown as they are so you can spot them in the Notes column.
  • Age is computed as of today. The two-digit year is guessed: years not yet past are read as 20xx, the rest as 19xx.
  • "Unknown code" means the region code is not in the current Ministry of Home Affairs list. Older NIKs from areas that have since been split often get this note, and it does not mean the NIK is fake.
  • This tool does not confirm that a NIK is registered. Only Dukcapil can answer that.

Not the Coretax bulk NIK validation, but the pre-check before it

Bulk NIK validation for tax purposes, such as matching employees' NIKs to tax numbers, is done on the DJP portal and checks every NIK against population data. This tool is not connected to it. What it does is the step before: cleaning the list of typos. A NIK with missing digits, an impossible date, or a wrong region code will certainly be rejected there; it is faster to find those here, in one paste, than to read rejection reports one by one. For supplier or customer NPWPs still in the 15-digit format, use the NPWP converter.

One trap that often happens before upload: a NIK stored as a number in Excel loses its last digit to 0. The Notes column here flags NIKs ending in 0000 or impossible dates, but a last digit changed from 5 to 0 cannot be detected; make sure the NIK column in Excel is formatted as Text from the start, as described in NIKs in Excel.

Frequently asked questions

Why does a NIK in Excel end in 0 or show as 1.27E+15?

Excel keeps only 15 significant digits, and a NIK has 16. Once a NIK is typed or pasted as a number, the last digit becomes 0 and it displays as 1.27118E+15. The data is permanently altered. Format the column as Text before typing, start the NIK with an apostrophe, or paste with Paste Special, then Text.

Do the formulas work in Google Sheets?

Yes. MID, VALUE, IF, DATE, and DATEDIF exist in Google Sheets with the same syntax. The argument separator follows the spreadsheet locale: commas for English locales, semicolons for locales such as Indonesian that use a decimal comma.

How many NIKs can be processed at once?

Up to 3,000 per run. Beyond that, split the list so the table stays light in the browser.

Is my employee list safe?

The list is never sent to a server. Processing happens in your browser, and closing the page discards everything. The only things that leave the device are the results you copy or download yourself.

Why are some NIKs flagged?

Because a part does not make sense: the region code is not in the Ministry list, the date does not exist, or the serial is 0000. The Notes column says which part. Older NIKs from regions that were later split are often flagged even though they are genuine.

Other tools