Konversi Tipe Date Ke Text Untuk Program BIO UN SMA/SMK

Contoh kasus kali ini merupakan bentuk bantuan kepada rekan kerja yang bingung mengkonversi tanggal ke format text ketika rekan kerja sedang mengis laporan Biodata Peserta Ujian Nasioan SMK/SMA. Program yang digunakan dikirim langsung oleh pusat dibangun menggunakan visual foxpro.

Ada satu kejanggalan ketika penulis membongkar database dari program BIO UN buatan Puspendik dimana seluruh field tanggal baik tanggal lahir, maupun tanggal lainnya tidak bertipe date tetapi bertipe character dengan lebar 6 character (entah apa maksud nya programmer BIO UN menggunakan tipe ini ?) sehingga ketika seorang siswa lahir tanggal 19/03/1996 maka data diisi ke field tanggal lahir dengan format 190392, perhatikan struktur table dari database Bio UN

Program tersebut menyediakan utilitas impor dari excel masalahnya operator sekolah terlanjur mengetik tanggal lahir dan tanggal lainnya dengan format yang seharusnya yaitu format date, sehingga ketika dilakukan proses impor ke dalam program dari file excel selalu gagal ! Mau tidak mau maka tanggal harus diformat ke tipe data yang diinginkan oleh program yaitu tipe karakter dengan lebar 6, masalahnya excel tidak memiliki fungsi untuk merubah tanggal ke tipe text kalaupun menggunakan fungsi =text(value) maka akan hasilnya tidak berupa tanggal tetapi bilangan integer, perhatikan gambar dibawah bagaimana ketika tanggal dikonversi ke text dengan fungsi =text() pada excel

Sedangkan program Bio UN menginginkan kolom tanggal tampak seperti pada kolom B pada gambar dibawah ini, maka tentu tidak ada fungsi khusus yang bisa melakukan selain kita membuat formula sendiri untuk merubahnya !

Nah bagaimana membuat formulanya ?? berikut penulis ambil contoh untuk merubah tanggal tipe date pada cell A2 menjadi tipe text dengan lebar enam digit pada cell B2

=CONCATENATE((IF(LEN(DAY(A2))=1;CONCATENATE(“0”;DAY(A2));DAY(A2)));(IF(LEN(MONTH(A2))=1;CONCATENATE(“0”;MONTH(A2));MONTH(A2)));(RIGHT(YEAR(A2);2)))

Wow !! panjang ?? bagaimana logikanya ?? berikut penjelasannya :

1. Formula diatas intinya mengambil tanggal dengan fungsi day, mengambil bulan dengan fungsi month dan mengambil year dengan fungsi year.
2. Melakukan pengecekan panjang (LEN) karakter hasil fungsi day dan month jika day dan mont panjangnya hanya satu maka akan digabung dengan angka 0 dengan fungsi CONCATENATE jadi ketika bulan januari akan menghasilkan angka 1 dengan fungsi month karena panjangnya hanya 1 karakter maka diawalnya ditambahkan angka 0 dengan fungsi CONCATENATE begitu pula untuk fungsi day !
3. Pada bagian akhir menggunakan fungsi year hasilnya akan berupa text sebanyak empat digit tahun namun kita ambil digit ke-3 dan ke-4 menggunakan fungsi right !
4. Hasil fungsi day akan menghasilkan text antara 01 – 12, sedangkan fungsi month akan menghasilkan text antara 01 -31 dan year berupa tahun dengan format 2 digit digabung seluruhnya dengan fungsi CONCATENATE sehingga hasil akhir pada Cell B2 adalah 190380

Untuk bahan latihan silahkan download file contoh artikel ini !

DOWNLOAD

Fungsi RIGHT

Fungsi RIGHT() merupakan bagian dari fungsi text, banyak fungsi text yang disupport oleh Microsoft Excel, namun kali ini akan membahas tenang fungsi right.

Fungsi right berguna untuk mengambil sejumlah character (termasuk spasi dan tanda baca) dari sebe;ah kanan berdasarkan parameter yang telah ditentukan dari satu cell tertentu.

Syntax
RIGHT(text,Jml_karakter_yang_akan_diambil)

Catatan

• Jml_karakter_yang_akan_diambil harus lebih besar dari satu atau sama dengan nol
• Jika Jml_karakter_yang_akan_diambil lebih besar dari jumlah karakter dalam variabel text, maka fungsi right akan menampilkan seluruh text sumber
• Jika Jml_karakter_yang_akan_ tidak disebutkan maka secara otomatis akan dianggap bernilai 1

Dari catatan diatas dapat digambarkan contoh sebagai berikut :

Penjelasan

• Pada contoh pertama mengambil 3 karakter dari kanan dari cel A1
• Pada contoh kedua mengambil 12 karekter dari sebelah kanan, hasilnya adalah ditampilkan seluruhnya karena jumlah huruf di cell A1
  hanya 11 karakter (lebih kecil dari yang diminta yaitu 12)
• Pada contoh terakhir menggunakan fungsi right tanpa menyertakan
  jumlah karater yang diambil, secara otomatis akan mengambil 1 karakter dari kanan

Conoh penggunaan fungsi RIGHT() bisa dilihat di studi kasus http://latihanexcel.co.cc/2010/02/hasil-studi-mahasiswa/

Hasil Studi Mahasiswa

Pada studi kasus kali ini akan membahas bagaimana membuat kartu hasil studi (KHS) untuk mahasiswa sekolah tinggi dimana setiap mata kuliah diberi nilai dengan huruf antara A sampai dengan E dengan bobot 4 untuk nilai A sampai 0 untuk nilai E. Setiap mata kuliah memiliki kode sendiri yang sifatnya unik terdiri dari 6 digit, digit terakhir merupakan jumlah SKS yang akan menjadi faktor pengali dengan bobot nilai.

Untuk jelasnya perhatikan gambar dibawah ini

Mari kita bahas satu persatu kolom diatas, kolom diatas yang akan dibuat formulanya adalah kolom Mutu, SKS, dan kolom Mutu x SKS. Ketiga kolom tersebut akan terisi secara otomatis saat anda mengisikan nilai pada kolom nilai.

Untuk membuat formula pada kolom nilai baris ke-13 kita gunakan fungsi if dengan formula sebagai berikut :

=IF(F13=”A”;4;IF(F13=”B”;3;IF(F13=”C”;2;IF(F13=”D”;1;O))))

formula diatas artinya jika cell F13 (kolom nilai) berisi A maka kolom mutu secara otomatis terisi dengan angka 4, jika B terisi angka 3, jika C terisi angka 2 jika nilainya D terisi angka 1
dan jika tidak cocok semuanya adalah 0, kurung tutup akhir formula jumlahnya sebanyak jumlah if di dalam formula, if didalam formula tersebut terdapat empat if jadi kurung tutupnya sejumlah empat buah.

Selanjutnya kita mengisi kolom SKS dengan ketentuan jumlah SKS adalah digit terakhir dari kode mata kuliah, misal jika kode mata kulian ESP203 maka SKS nya adalah 3, untuk itu kita gunakan fungsi text yaitu
fungsi right, namun hasil dari fungsi text adalah character meskipun secara visual berupa angka, karena tipe datanya character maka tidak bisa dikalkukasikan, agar hasilnya berubah menjadi numeric sehingga mudah untuk dikalkulasikan hasil dari fungsi right kita konversi ke numeric dengan fungsi value, sehingga kolom SKS akan kita isi dengan rumus sebagai berikut :

=VALUE(RIGHT(D13;1))

Setelah kolom SKS terisi dengan formula diatas saatnya mengisi kolom
Mutu x SKS dengan dengan menggunakan formula perkalian matematika bisa yaitu :

=G13*H13

Sedangkan untuk mengisi kolom total dari kolom SKS dan Mutu x Bobot menggunakan fungsi database yaitu fungsi sum masing masing dengan rumus :

Total kolom SKS

=SUM(H13:H19)

Total mutu x bobot

=SUM(I13:I19)

Dan index prestasi diisi dengan formula pembagian antara total mutu x bobot dengan total sks dan membulatkan sampai dengan dua angka dibelakang koma menggunakan fungsi round dengan rumus :

=ROUND(I20/H20;2)

Terakhir adalah menetukan beban SKS yang boleh diambil oleh mahasiswa
yang bersangkutan dengan ketentuan jika indeks prestasinya kurang dari sama dengan 3 boleh mengambil maksimal 24 SKS, jika indeks prestasi kirang dari 2,75 boleh mengambil 22 SKS dan jika indeks prestasinya kurang dari 2 maka maksimal boleh mengambil 18 SKS, dengan menggunakan fungsi if maka ketentuan tersebut dibuat rumusan sebagai berikut :

=IF(I22>=3;24;IF(I22>2,75;22;IF(I22>2;20;18)))

Nah sampai disini anda telah mempelajari fungsi if, right, sum, value, dan round. Silahkan download file latihan excel nya untuk anda pelajari lebih lanjut.