Beberapa minggu yang lalu saya ketemu teman lama yang sudah lama berprofesi sebagai guru disalah satu sekolah di Kota Medan, selain menjadi guru beliau juga merangkap sebagai operator di sekolah. Suatu ketika beliau kesulitan dalam mencari data tanggal, bulan, tahun lahir dan jenis kelamin siswa dan guru hanya dengan mengandalkan NIK yang ada di KTP. Lalu beliau bertanya kepada saya, bisa tidak diolah data tanggal lahirnya hanya menggunakan NIK menggunakan Excel? Saya bilang bisa, walaupun belum saya coba. 😅

NIK atau Nomor Induk Kependudukan adalah nomor identitas penduduk yang bersifat unik atau khas, tunggal dan melekat pada seseorang yang terdaftar sebagai penduduk Indonesia (wikipedia). NIK terdiri dari 16 digit yang disusun berdasarkan kode provinsi, kabupaten, kecamatan, tgl lahir dan nomor urut.

Contoh untuk NIK: 1271061112170001

KODE KETERANGAN 12 Kode Provinsi Sumatera Utara 71 Kode Kota Medan 06 Kode Kecamatan Medan Deli 11 Tanggal Lahir, untuk perempuan +40 12 Bulan Lahir dalam angka 17 2 digit akhir Tahun Lahir 0001 Nomor Urut Registrasi Kependudukan

Berdasarkan susunan dari NIK diatas, dapat dengan mudah mengetahui tanggal lahir dan jenis kelaminnya. Untuk Jenis Kelamin-nya bisa di lihat dari kode tanggal pada NIK-nya jika lebih dari 40 berarti pemilik NIK tersebut adalah perempuan, jika kurang dari 40 berarti Laki-Laki.

Untuk membuat tanggal lahir dari NIK, Anda bisa menggunakan Fungsi MID.

Fungsi MID digunakan untuk mengembalikan sejumlah karakter tertentu dari suatu string teks, dimulai dari posisi yang telah ditentukan berdasarkan jumlah karakter.

 Penulisan fungsinya adalah:

= MID ( text ; start_num; num_chars )

Text: merupakan teks yang membuat karakter yang ingin di ekstrak.

Start_num: merupakan posisi dari karakter pertama yang ingin di ekstrak.

Num_chars: merupakan jumlah karakter yang ingin dikembalikan.

Untuk memudahkan menghitung posisi dari karakter NIK perhatikan tabel berikut ini:

NIK	1	2	7	1	0	6	1	1	1	2	1	7	0	0	0	1
Posisi ke-	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16

Dari tabel di atas terlihat posisi tanggal lahir ada di karakter ke 7 dan 8, posisi bulan lahir pada karakter ke 9 dan 10, sedangkan untuk tahun lahir pada posisi ke 11 dan 12.

Jadi misalnya NIK berada di sel A2 maka rumus excel untuk membuat tanggal lahir dari NIK adalah:

= MID ( A2; 7; 2)

Karena untuk tanggal ada penambahan 40 maka dari rumus menampilkan tanggal di atas, kita ubah dan tambahkan fungsi IF untuk memeriksa apakah tanggal bernilai lebih dari 40 atau tidak, jika lebih maka nilainya harus di kurangi 40 untuk menampilkan tanggalnya.

Untuk itu rumus untuk menampilkan tanggal lahirnya di ubah menjadi:

=IF(--MID(A2;7;2)>40;MID(A2;7;2)-40;MID(A2;7;2))

Fungsi IF diatas digunakan untuk memeriksa atau melakukan pengujian, apakah tanggal lahir dari NIK bernilai lebih dari 40 atau tidak, perhatikan sebelum fungsi MID yang pertama ada tanda/simbol (--) atau double unary / double negative yang fungsinya adalah untuk mengubah nilai TRUE atau FALSE menjadi Satu dan Nol.

Hasil Ekstrak dari fungsi MID ini berupa string atau teks sehingga akan gagal jika dilakukan pengujian dengan operator pembanding seperti (>) lebih besar dari, karena bukan berupa angka, untuk itu sebelum MID pertama ditambahkan tanda double unary untuk mengubahnya menjadi format angka atau number.

Setelah mengetahui cara menampilkan tanggal lahir dari NIK, selanjutnya adalah menampilkan bulan lahirnya, dan rumus excel yang digunakan adalah:

=MID(A2;9;2)

Sedangkan untuk menampilkan tahun lahirnya, rumus excel yang digunakan adalah:

=IF(--MID(A2;11;2)+2000>YEAR(TODAY());19&MID(A2;11;2);20&MID(A2;11;2))

Fungsi IF disini digunakan untuk memeriksa setelah dua digit tahun dari NIK di tambah 2000 jika hasilnya lebih dari tahun sekarang, maka hasil yang ditampilkan adalah angka 19 & 2 digit tahun dari NIK, jika lebih kecil dari tahun sekarang maka yang ditampilkan adalah angka 20 & 2 digit tahun dari NIK.

Fungsi diatas akan berlaku untuk orang yang usianya maksimal 100 tahun, jika usianya lebih dari 100 tahun maka fungsi IF di atas tidak akan sesuai hasilnya.

Setelah mengetahui rumus cara mendapatkan tanggal, bulan, dan tahun lahir dari data NIK, selanjutnya adalah menggabungkan ketiga fungsi tersebut ke dalam satu sel. Agar menjadi format Date atau tanggal yang benar sebaiknya menggunakan tambahan fungsi lagi yaitu fungsi DATE.

Penulisan rumusnya adalah:

=DATE ( Tahun_Lahir; Bulan_Lahir; Tanggal_Lahir)

Sehingga rumus excel yang digunakan adalah:

=DATE(IF(--MID(A2;11;2)+2000>YEAR(TODAY());19&MID(A2;11;2);20&MID(A2;11;2)); MID(A2;9;2); IF(--MID(A2;7;2)>40;MID(A2;7;2)-40;MID(A2;7;2)))

Terima kasih,
Semoga bermanfaat!