Cara membuat Tabel umur Sertifikat Otomatis

Tulisan kali ini, saya ingin membuat cara membuat tabel otomatis untuk umur sertifikat sertifikat Pelaut yang kita miliki sebagai seorang Pelaut, dimana dengan tabel Excel ini sedikit membantu untuk memberi tahu atau mengingatkan kita bahwa masa berlaku dari sertifikat Pelaut yang kita miliki kapan harus di update (updating ataupun di revalidasi).

Untuk membuat tabel umur sertifikat otomatis kita menggunakan salah satu fungsi rumus di excel yaitu Fungsi DATEDIF dimana fungsi ini merupakan salah satu fungsi di Microsoft Excel yang digunakan untuk menghitung durasi waktu dengan membuat selisih tanggal dalam satuan hari, bulan ataupun tahun.

Fungsi Excel DATEDIF yang akan saya bahas sedikit disini sesuai yg saya tahu dan mengerti kemudian di terapkan atau di apikasikan untuk menghitung jumlah tahun, bulan, atau hari pada umur sertifikat keterampilan Pelaut (COP) yang biasa di haruskan untuk di revalidasi atau di update setiap 5 tahun sekali. Fungsi Datedif secara umum digunakan untuk menghitung umur atau menghitung masa kerja seseorang di excel.

Fungsi DATEDIF sendiri di menu insert function tidak muncul artinya kita harus mengetikkan rumus sendiri dengan mengetik rumusnya DATEDIF secara manual di formula bar atau pada cell yg aktif di mana kita akan menggunakan fungsi rumusnya.

Kode Satuan Pada Fungsi DATEDIF Excel yang bisa kita gunakan untuk mendapatkan informasi selisih tanggal, bulan dan tahun di excel adalah sebagai berikut:

Kode satuan "Y" -> jumlah total tahun yang sudah berlalu diantara dua tanggal.
Kode satuan "M" -> jumlah total bulan yang sudah berlalu diantara dua tanggal.
Kode satuan "D" -> jumlah total hari diantara dua tanggal.
Kode satuan "MD" -> selisih hari dalam Tanggal Awal dan Tanggal Akhir. Untuk Selisih bln dan thn dari tanggal diabaikan.
Kode satuan "YM" -> selisih bulan dalam Tanggal Awal dan Tanggal Akhir. Untuk selisih Hari dan tahun dari tanggalnya diabaikan.
Kode satuan "YD" -> selisih hari dalam Tanggal awal dan Tanggal khir. Selisih Tahunnya dari tanggal diabaikan.

Sebagai informasi tambahan bahwa ketika menggunakan Fungsi Datedif Excel ada beberapa hal yang perlu di perhatikan saat menggunakan fungsi DATEDIF pada microsoft excel ini:

jika fungsi rumus DATEDIF yang kita gunakan menghasikan Error #NAME?, kemungkinannya adalah kita salah menuliskan fungsi atau ada yang kurang pas ketikan rumusnya Karena fungsi ini tidak muncul sebagai autocomplete saat menuliskan rumus biasanya pengguna excel salah menuliskan fungsi ini sebagai DATEIF yang seharusnya ditulis DATEDIF (kurang huruf D)
Dan jika nilai Tanggal Awal Lebih besar daripada Tanggal Akhir maka rumus Datedif akan muncul nilai error #NUM! Atau salah penempatan tanggal dimana argumen tanggal yang dimasukkan terbalik.

Kembali ke topik awal, jadi untuk membuat tabel otomatis umur sertifikat CoP pelaut, kita perlu tahu rumus atau formula apa yang akan kita gunakan. Diatas sudah sedikit di kupas mengenai formula dan penggunaannya kemudian untuk melengkapi penandaan dan pemberitahuan tiap cell tabel sertifikat kita beri warna sesuai umur sertifikat dimana:

Warna Hijau jika umur sertifikat masih di rentang 1 - 4 tahun.
Warna kuning jika umur sertifikat sudah memasuki 4 dan menuju ke 5 tahun.
Warna Merah jika umur sertifikat sudah lewat dari 5 tahun.

Mulai saja kita lanjut ke langkah langkah pembuatan tabel nya.

Buka aplikasi MS office Excel, saya contohkan seperti gambar di bawah, dengan cell A1 di isi nomer dan cell B1 di isi dengan - Tanggal hari ini (hari yg berlangsung), silahkan isi dengan Format /rumus: =today(), agar secara otomatis tanggal ter update setiap kali kita membukanya.
Di cell selanjutnya C1 saya contohkan tulis dengan nama Sertifikat CoP -> Isi dengan sertifikat COP yg kita miliki
Berikutnya cell D1 isi dengan Issued -> Tanggal terbit misalkan terbit tanggal 05 November 2019.
Di cell berikutnya E1 isi dengan 4 Tahun -> artinya Umur sertifikat sudah memasuki 4 tahun dari tanggal terbit diatas menjadi 05 November 2023 dan bila umur sertifikat sudah melewati 4 tahun maka kita kasih warna kuning sebagai peringatan bahwa sertifikat akan mendekati 5 tahun dan harus segera di update, fungsi rumus yg harus diisi pada cell adalah sebagai berikut: Fx =EDATE(D3,48) -> angka 48 artinya 48 atau 4 tahun.
Di cell F1 isi dengan 5 tahun - > Umur sertifikat sudah 5 tahun misal dari tanggal terbit di atas ke 5 tahun adalah 05 November 2021, rumus yang di gunakan di cel ini adalah Fx =EDATE(D2,60) -> Angka 60 artinya 60 bulan atau 5 Tahun, lihat gambar di bawah:
Dan cell yang terakhir G1 isi dengan Umur sertifikat dengan menggunakan rumus Fx ="" &DATEDIF(D2,B2,"Y")&"Tahun"&DATEDIF(D2,B2,ym")&"Bulan"&DATEDIF(D2,B2,"md")&"Hari"

Bila sudah diisi sesuai dengan rumus fungsi masing-masing maka ketika kita membuka tabel umur sertifikat akan terampil otomatis seperti dibawah ini:

Di sel umur sertifikat terampil detail dari mulai tahun, bulan dan hari, contoh di sel sertifikat BST umur sertifikat di tanggal 3 oktober adalah 1 tahun 10 bulan 28 hari dan warna masih hijau di karenakan umur sertifikat baru 1 tahun.

Langkah selanjutnya untuk memberi warna tiap sel sertifikat secara otomatis, kita bisa menggunakan fungsi Conditional Formatting.

Fungsinya Conditional formating sendiri di ms excel adalah digunakan untuk mengubah format sel ataupun range excel secara otomatis bila memenuhi syarat atau kriteria yang kita buat.

Conditional Formatting kita gunakan di range Sertifikat agar kita tahu sertifikat mana yang sudah berumur 4 tahun atau 5 tahun dengan melihat warna sel, apakah hijau, kuning atau merah.

Fungsi ini di gunakan hanya untuk mempermudah pembacaan tabel jika tidak di gunakan juga tidak masalah, untuk lebih detail tentang Conditional Formatting bisa di browsing sendiri di Internet.

Untuk setting bisa kita sorot range Tanggal hari ini (today) dan pilih menu Conditional Formatting di halaman Home.

Kemudian manage rule dengan Conditional format (format only cells that contain)

Less than or equel to 4 year (green)/ kurang dari atau sama dengan 4 tahun (hijau), di bagian rumusnya hilangkan tanda dolar ($) dan pada format warna pilih warna hijau lalu klik ok.
Between 4 year - 5 year (yellow)/ antara 4 tahun - 5 tahun (kuning), sama ketika masuk di rule nya dan pada rumus muncul tanda dolar, hapus dan seperti rule 1 pilih format warna kuning lalu klik ok.
Greater than or equel to 5 year (red)/ lebih dari atau sama dengan 5 tahun (merah). Sama dengan rule 1 dan 2 ketika muncul tanda dolar ($), hapus lalu pilih format warna merah dan klik ok.

Demikian sedikit share tips membuat tabel otomatis umur sertifikat pelaut yang pernah saya buat.