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:
ReplyDeletekalau saya mau ngetik 502.01 kenapa berubah jd tanggal ya gan.
tolong di bantu gan.
Coba format numbernya di rubah ke number
Deletesama nih kasusnya Gan. 52.01.02 malah jadi Date, diubah ke number jadi 18995
DeleteKlw yg ini format text aja mas, atau ketik tanda kutip di depannya '52.01.02
Deletekasus saya adalah saya membuat laporan dengan menyertakan tanggal. pada proses pemasukan data yang saya input sebelumnya menggunakan format Indonesia (2/6/2020 atau 2 Juni 2020). masalah yang saya hadapi adalah sistem excel mengenal penulisan tanggal yang sama input adalah berbeda yakni tanggal 2/6/2020 dianggap 6 Februari 2020. apakah ada solusi agar sistem excel mengenal format tanggal yang telah saya buat dimana saya telah input data tersebut sangat banyak dan saya butuh mengurutkan tanggal tersebut secara otomatis dari bulan Januari sampai sekarang.
Deletekalau kasus menulis tanggal 2/6/2020 dianggap 6 Feb 2020, coba ubah setting bahasa dari excel dari english ke indonesia
DeleteSaya mencoba mengurutkan data tanggal lahir. Dari 622 data yang saya coba urutkan, hanya 377 data yang berhasil diurutkan dari oldest to newest. Sisanya tidak bisa diurutkan. Saya sudah mencoba mengubah format penulisan tanggal, bahkan sudah mengubah semuanya ke format text dan kemudian saya coba ubah kembali ke format date. Tapi masih tidak bisa diurutkan semuanya. Apakah solusi yang bisa saya lakukan?
ReplyDeleteRUMUSMU SALAH MAS, YANG JELAS CONTOHMU ITU SALAH, KALIAN HARUS UBAH SEPERTI INI
ReplyDelete(01.02.2021)
=VALUE(LEFT(M3,2))
=VALUE(MID(M3,4,2))
=VALUE(RIGHT(M3,4))
=DATE(P3,O3,N3)