Solusi Masalah Penulisan Tanggal Di Excel

Pembaca je-xcel yang budiman. Barangkali kita pernah menangani tugas-tugas menggunakan aplikasi excel berkenaan dengan data-data tanggal dan waktu. Kemudian kita mencoba membuat  rumus excel untuk mendapatkan selisih antara dua tanggal. 

Kadang-kadang hasil yang didapatkan tidak relevan, atau bahkan error. Padahal rumus sudah dituliskan secara benar. 

Apakah penyebab dari masalah ini? Dan apa solusinya? Mari kita diskusikan bersama...

Penyebab Umum Masalah Penulisan Tanggal Tidak Benar Atau Error

Masalah 1: Struktur atau Pola Penulisan Tanggal  Tidak Tepat 

Hal ini berkaitan dengan setting komputer.

Kita sebagai orang indonesia sudah terbiasa menuliskan tanggal dengan struktur hari/bulan/tahun. Namun tidak semua komputer di setting seperti itu. Malah lebih banyaknya setting penulisan tanggal menggunakan struktur bulan/hari/tahun.

Jika kejadiannya seperti ini. Mungkin saja rumus tidak menghasilkan nilai error. Tetapi hasilnya tidak relevan. Hal ini terjadi karena angka bulan menjadi tanggal, dan angka tanggal menjadi bulan. Error akan terjadi jika angka bulan lebih dari 12.

Masalah struktur penulisan tanggal yang tidak tepat, biasanya ketika kita bekerja dengan team. Dimana tidak semua anggota mengetahui cara yang benar dalam menuliskan tanggal di excel. Kemudian seseorang ditunjuk untuk mengumpulkan data, membuat summary dan rekapitulasi data untuk dijadikan laporan.

Parahnya, hal ini bisa terjadi tanpa disadari, karena petugas yang mengumpulkan data bisa menganggap data tersebut sudah benar, karena pada saat kalkulasi tidak menimbulkan data error.

Akibatnya: Laporan anda bisa salah dan si Bos pun marah (kalau ketahuan…)

Solusi 

• Jika anda bekerja dengan team dan anda bertugas untuk membuat rekapitulasi dan laporan. Sebaiknya anda membuat template sehingga user atau anggota team lainnya tidak mengetikan tanggal lengkap secara langsung dalam sel.
• Buatkan tiga kolom isian terpisah untuk masing-masing isian tanggal, bulan dan tahun.
• Sedangkan untuk mendapatkan tanggal lengkapnya, kita bisa menggunakan rumus fungsi DATE
• Hal tersebut sangat efektif menghindari kesalahan cara penulisan tanggal di excel.
• Rumus DATE dapat dituliskan sebagai berikut:   
• =DATE(year,month,day) dimana year=tahun, month=bulan, day=hari (nomor urut hari dalam satu bulan)
• Contohnya =DATE(2015,8,21) akan menghasilkan data tanggal 8/21/2015 jika menggunakan format USA (mm/dd/yyyy), dan menghasilkan data tanggal 21/8/2015 jika menggunakan format Indonesia (dd/mm/yyyy). Tapi  format tidak lagi menjadi masalah. Yang penting kita sudah mendapatkan data tanggal yang benar dan dapat dimasukan dalam operasi matematika..
• Berikut screenshoot contoh rumus excel DATE

Masalah 2: Penulisan Tanggal Menggunakan Format Text

Dalam hal ini, sel menggunakan format text atau penulisan tanggal didahului tanda petik satu atau apostrope. Dalam beberapa kasus, formula dapat berjalan normal, tapi dalam beberapa kasus, mungkin juga formula menghasilkan nilai error.

Masalah ini juga sering dijumpai jika kita bekerja dengan data yang merupakan hasil import atau export dari aplikasi lain. Selain didahului apostrope dan diformat text, data tanggal yang diimport dari aplikasi lain juga kadang bercampur dengan tanda spasi.

Karena merupakan text, maka data tanggal tersebut tidak bisa diproses  dalam operasi matematika.

Untuk mengatasi masalah ini, maka kita terlebih dahulu harus mengetahui struktur penulisan text tanggal yang diperoleh  import/export aplikasi lain. Anda bisa mengecek terlebih dahulu keseluruhan data, sehingga dapat diketahui posisi tanggal dan bulannya.

Contohnya:

Sebuah kolom berisi data tanggal dituliskan sebagai berikut (tanpa tanda petik): 

Sel A2:    "  01.02.2016"

Sel A3:    "  27.03.2015"

Sel A4:    "  15.07.2015"

Dengan memperhatikan contoh tanggal diatas, kita dapat memastikan bahwa penulisan tanggal mengikuti pola "  dd.mm.yyyy".

• Angka hari (dd) menempati urutan karakter ke 3 dan 4 (setelah dua buah spasi)

• Angka bulan (mm) menempati urutan karakter ke-6 dan ke-7

• Angka tahun (yyyy) menempati urutan karakter ke-9 s.d ke-12

Nah, sekarang bagaiana cara mengkonversi text tanggal tersebut, menjadi data tanggal yang bisa dikalkulasi.

Untuk keperluan tersebut, kita bisa menggunakan bantuan fungsi VALUE dan fungsi MID serta menggabungkan hasilnya menggunakan fungsi DATE.

• Angka hari (dd)   =VALUE(MID(text_tanggal,3,2))

• Angka bulan(mm)   =VALUE(MID(text_tanggal,6,2))

• Angka tahun(yyyy) =VALUE(MID(text_tanggal,9,4))

Kemudian, angka tahun, bulan dan hari tersebug digabungkan menggunakan fungsi DATE. 

Rumusnya dapat dituliskan sebagai berikut:

= DATE(VALUE(MID(text_tanggal,9,4)), VALUE(MID(text_tanggal,6,2)), VALUE(MID(text_tanggal,3,2)))

Atau jika text_tanggal ditempatkan pada sel A2, maka rumus dapat dituliskan seperti di bawah ini:

=DATE(VALUE(MID(A3,9,4)),VALUE(MID(A3,6,2)),VALUE(MID(A3,3,2)))

Perhatikan screenshot contoh berikut:

Sampai pada tahap ini, kita sudah bisa mendapatkan data tanggal yang bisa dilakukan operasi matematika dalam proses selanjutnya.

Ringkasan.

Masalah cara penulisan tanggal, baik yang disebabkan kesalahan pengetikan ataupun karena data import/export dari aplikasi lain dapat diatasi dengan mengambil atau memecah porsi tahun, bulan dan hari, kemudian menggabungkannya kembali menggunakan fungsi DATE untuk mendapatkan data tanggal utuh.

Semoga bermanfaat.

Belajar Excel..! Excellent !

Baca juga, Tips dan Tutorial Belajar Excel Lainnya:

• Mengatasi Error To Many Different Cells Format
• Menampilkan Nama Hari dan Bulan Dalam Bahasa Indonesia
• Kenapa Header Kolom Berubah Menjadi Angka

Mengatasi Error "Too Many Different Cell Formats"

Pesan Error "Too Many Different Cell Format" mulai sering saya jumpai beberapa tahun lalu ketika saya harus merekap data excel dari beberapa unit kerja. Ketika data dikumpulkan menjadi 1 file excel dengan cara copy paste, tiba-tiba muncul pesan error seperti ini.

Pesan error tersebut muncul jika file dibuka/diedit menggunakan komputer perusahaan yang masih menggunakan Office 2003. Jika file tersebut dibuka di komputer pribadi yang menggunakan excel 2007, pesan error tidak muncul dan pekerjaan dapat dilanjutkan. Sayangnya ketika file tersebut dibuka kembali menggunakan excel 2003, error kembali muncul dan otomatis komputer melakukan proses recovery, dan hasilnya : file rusak, semua formatnya cell-nya hilang. :-(

Hal ini cukup memusingkan kepala dan karena ketidaktahuan saya, dulu saya mengira ini adalah akibat ulah virus. Mungkin ada orang yang iseng membuat virus cara merusak file excel dan menyampaikan pesan too many different cell formats setiap kali akan menambahkan format baru. Itu fikiran jelek saya, tapi itu dulu... :-)

Dan kasihan Si Virus jadi tertuduh, padahal ia sama sekali tidak berdosa atas masalah ini :-). Ada juga teman yang mengatakan, wah ini karena terlalu kebanyakan rumus ...! Atau kebanyakan link..!

Penyebab  "To Many Different Cell Format"

Usut punya usut, setelah browsing di internet dan bertanya pada halaman bantuan excel, ternyata penyebabnya sangat jelas dan sesuai pesan error dari excel. Yaitu "Too Many Different Cell Formats", artinya "Terlalu Banyak Format Sel Yang Berbeda".  Sebelumnya kok enggak geh ya dengan arti pesan error tersebut??

Dan ternyata untuk kasus yang saya alami, masalahnya terletak pada versi Excel yang digunakan. Microsoft Excel 2003 hanya dapat menampung 4000 cell style yang berbeda/unik. Cell style atau cell format adalah kombinasi pengaturan format sell termasuk number, alignment, font, border, fill dan protection. Jika satu property saja dari format cell yang berbeda, maka dapat dikatakan bahwa cell tersebut memiliki style yang berbeda. Hal ini dapat diilustrasikan sebagai berikut:

Format Cell yang Berbeda

Gambar diatas mengilustrasikan format cell yang berbeda-beda, meskipun berisi text yang sama terdiri satu karakter huruf A, namun masing-masing cell memiliki format yang berbeda dan unik satu sama lainnya. Karena kreatifitas user, biasanya tanpa disadari semakin banyak format cell yang berbeda-beda dan pada akhirnya akan mencapai batas 4000 format cell yang berbeda.

Microsoft excel 2007, dapat menampung lebih banyak format cell yang berbeda yaitu sebanyak 64.000. Sehingga jika jika file excel 2003 yang sudah over format dibuka menggunakan excel 2007, maka pesan error tidak akan muncul. Namun celakanya jika file tersebut kembali dibuka menggunakan versi 2003, maka excelnya pun menjadi "mabuk" format.

Pantesan saja, masalah ini sering muncul, setelah munculnya excel-excel versi yang baru yang lebih canggih dan mampu menampung format yang lebih banyak. Teman-teman yang menggunakan laptop atau komputer pribadi sudah menggunakan versi excel yang lebih tinggi biar tidak ketinggalan zaman.

"Karena loyalitas yang sangat tinggi, banyak teman-teman menggunakan komputer pribadi, untuk menyelesaikan pekerjaan admin kantor. Sangat  Salut ... dan Hebat lah :-) "

Bagaimana Cara Mengatasinya.

• Jika kita bekerja dalam tim sebaiknya dibuat kesepakatan untuk menggunakan 1 versi excel saja untuk keperluan pengolahan data. Apalagi jika petugas yang mengumpulkan data final masih menggunakan excel jadul 2003, sementara data unit menggunakan excel mutakhir seperti versi 2007, 2010 maupun 2013. Sudah dapat dipastikan bahwa problem to many different cell format akan terjadi.
• Gunakan jenis font yang sama untuk semua data, dan hindari penggunaan size font yang bervariasi.
• Jangan terlalu banyak variasi dalam warna, alignment, border
• Dan lain-lain pokoknya jangan terlalu banyak variasi format dech... Memang cukup menggangu kreatifitas, tapi pilih mana? tampilan bagus dan ngejreng tapi error dan file rusak? atau tampilan sederhana tapi aman dan nyaman?
• Jika terlanjur error, buka file yang error tersebut menggunakan OpenOffice dan save sebagai excel 2000. Cara ini bisa sedikit membantu terutama jika kerusakan file belum begitu kompleks.
• Atau jika ada cukup waktu clear semua format dan ganti menjadi format yang lebih sederhana dan konsisten.
• Usulkan ke Bos Anda supaya dapat disetujui budget IT berupa instalasi program microsoft excel yang paling mutakhir di semua komputer perusahaan / instansi dimana anda bekerja... Nah kalau yang ini mungkin agak sulit...

Demikian semoga bermanfaat :-)