Menghitung Umur dan Masa Kerja Berdasarkan NIP PNS (Microsoft Excel)

Pada pembahasan kali ini saya akan memberikan trik bagaimana memecah NIP (Nomor Induk Pegawai) PNS  (Pegawai Negeri Sipil) yang berjumlah 18 digit pada Microsoft Excel.

Memecah NIP dapat memberikan kita keuntungan, yaitu kita tidak perlu mengentri kolom tanggal lahir, tmt cpns, dan jenis kelamin, karena semua informasi tersebut sudah terdapat dalam NIP yang baru.

Komposisi NIP

Keterangan:

- digit ke 1- 8 : tanggal lahir (tahun-bulan-hari)

- digit ke 9 - 14 : tmt cpns (tahun-bulan)

- digit ke 15 : jenis kelamin 

- digit ke 16 - 18 : no urut 

1. Memecah Tanggal Lahir

Tanggal lahir yang terdapat pada NIP, formatnya adalah yyyy-mm-dd, sementara bila kita ingin menggunakannya dalam ms Excel, kita perlu merubahnya menjadi dd-mm-yyyy.

Untuk memecah/mengambil tanggal lahir kita menggunakan rumus MID, dimana NIP berada pada kolom D6 sampai D10.

=(MID(D6;7;2))&"-"&(MID(D6;5;2))&"-"&(MID(D6;1;4))

Keterangan rumus:

Rumus	Keterangan
(MID(D6;7;2))	mengambil sebanyak 2 digit setelah digit ke 7 dari D6 (mulai dari sebelah kiri)
&"-"&	menambahkan karakter "-" sebagai pemisah tanggal
(MID(D6;5;2))	mengambil sebanyak 2 digit setelah digit ke 5 dari D6 (mulai dari sebelah kiri)
(MID(D6;1;4))	mengambil sebanyak 4 digit setelah digit ke 1 dari D6 (mulai dari sebelah kiri)

2. Memecah TMT CPNS
TMT CPNS dihitung sejak tanggal 1 pada bulan dimana CPNS mulai aktif bekerja, oleh karena itu tidak dituliskan tanggal TMT CPNS pada NIP.

Untuk memecah/mengambil TMT CPNS kita masih menggunakan rumsu MID.
="01"&"-"&(MID(D6;13;2))&"-"&(MID(D6;9;4))
Keterangan rumus:

Rumus	Keterangan
"01"	menuliskan karakter "01"
&"-"&	menambahkan karakter "-" sebagai pemisah tanggal
(MID(D6;13;2))	mengambil sebanyak 2 digit setelah digit ke 13 dari D6 (mulai dari sebelah kiri)
(MID(D6;9;4))	mengambil sebanyak 4 digit setelah digit ke 9 dari D6 (mulai dari sebelah kiri)

3. Menampilkan Jenis Kelamin
Kode pada NIP untuk menampilkan jenis kelamin ada pada digit ke 15, dimana 1 untuk laki-laki dan 2 untuk perempuan

Kali ini kita akan mengkombinasikan rumus MID dengan IF, dengan rumus sebagai berikut:
=IF(MID(D6;15;1)="1";"Laki-laki";"Perempuan")
Keterangan rumus:

Rumus	Keterangan
IF(MID(D6;15;1)="1";"Laki-laki";"Perempuan"	jika karakter pada digit ke 15 sama dengan 1 maka tuliskan "laki-laki", jika bukan maka tuliskan "Perempuan"

4. Menghitung Umur
Setelah kita memiliki field yang berisi tanggal lahir, maka kita bisa mulai menghitung umur pegawai, caranya adalah dengan rumus:
=YEAR(TODAY())-YEAR(H6)
Keterangan rumus:

Rumus	Keterangan
YEAR(TODAY())	menampilkan tahun saat ini, mis : 2015
-YEAR(H6)	mengurangkan tahun saat ini dengan tahun pada kolom H6 yang berisi tanggal lahir

5. Menghitung Masa Kerja
Setelah kita memiliki field yang berisi tmt cpns, maka kita bisa mulai menghitung masa kerja pegawai, caranya adalah dengan rumus:
=YEAR(TODAY())-YEAR(I6)&" tahun"
Keterangan rumus:

Rumus	Keterangan
YEAR(TODAY())	menampilkan tahun saat ini, mis : 2015
-YEAR(I6)&" tahun"	mengurangkan dengan nilai tahun pada kolom I6 yang berisi tmt cpns dan tambahkan teks" tahun"

Demikian, tips dari saya, semoga bermanfaat buat rekan-rekan semua.