Tips Memanfaatkan Blog Ini

  1. Perhatikan tanggal penulisan dan tanggal update artikel. Semakin baru tanggal-tanggal ini, semakin valid informasi yang tercantum.
  2. Harap membaca komentar-komentar terkait sebelum bertanya; bisa jadi hal yang ingin Anda tanyakan sudah terjawab.
  3. Pertanyaan dapat disampaikan lewat bagian komentar atau lewat akun Twitter @asyafrudin.

Rabu, 05 Januari 2011

Merubah Text Tanggal Menjadi Date di Excel (2)

Tulisan ini adalah sambungan dari Merubah Text Tanggal Menjadi Date di Excel. Pada tulisan tersebut dijelaskan rumus yang dapat digunakan untuk mengolah teks tanggal dengan format DD-MM-YYYY. Dalam tulisan kali ini akan dijelaskan rumus yang dapat digunakan untuk merubah teks tanggal dengan format DD MMMM YYYY (contoh: 04 Agustus 2010) menjadi format tanggal (DATE) yang dimengerti Excel.

Rumus yang dimaksud adalah sebagai berikut:

=DATE(RIGHT(A1, 4), VLOOKUP(MID(A1, SEARCH(" ", A1)+1, SEARCH(" ", A1, SEARCH(" ", A1)+1)-SEARCH(" ", A1)-1), Sheet2!$A$1:$B$13, 2), LEFT(A1, SEARCH(" ", A1)-1))

Cara kerja rumus di atas pada dasarnya adalah mengambil 4 digit tahun, kata di antara tanda spasi sebagai bulan untuk dirubah menjadi digit, dan digit sebelum spasi pertama sebagai tanggal. Misalnya 04 Agustus 2010 akan dipecah menjadi 2010, Agustus yang sudah diubah menjadi 8, dan 04. Selanjutnya setiap angka tersebut dimasukan ke dalam fungsi DATE untuk dijadikan tanggal yang dimengerti Microsoft Excel.

Catatan:
  • A1 adalah referensi cell yang berisi teks tanggal.
  • Sheet2!$A$1:$B$13 adalah tabel referensi bulan pada Sheet2 dengan isi sebagai berikut:
  • Isi tabel referensi di atas diurutkan berdasarkan abjad nama bulan. Hal ini diharuskan dalam penggunaan fungsi VLOOKUP.
  • Ubah A1 dan tabel referensi di atas sesuai dengan kebutuhan sebelum menggunakan rumus ini.
  • Tanda koma "," dalam rumus di atas ada kalanya perlu diubah menjadi tanda titik koma ";". Hal ini tergantung pada konfigurasi pemisah parameter fungsi pada Microsoft Excel masing-masing komputer.
  • Rumus di atas sudah dicoba menggunakan Microsoft Excel 2007.
--
Amir Syafrudin

29 komentar:

  1. Bagus sekali artikelnya dan saya sudah berhasil mencobanya. Saya mohon bantuannya bagaiman menterjemahkan tanggal menjadi text. Contoh tanggal 15/12/2012 saya format menjadi 15 Desember 2012, kemudian saya hanya ingin mengambil angka 15 menjadi "lima belas", kemudian saya coba menggunakan format "terbilang" digabung dengan format "mid" tapi hasilnya tidak sesuai yang diharapkan, mohon bantuannya, terima kasih sebelumnya.

    BalasHapus
    Balasan
    1. Kalau hanya mengandalkan fungsi yang tersedia di Excel, untuk merubah dari 15/12/2012 itu diperlukan minimal 2 langkah. Pertama, kita ambil dulu angka "12" (bulan) dengan fungsi MID. Kedua, kita gunakan VLOOKUP untuk merubah "12" menjadi "Desember".

      Untuk penggunaan TERBILANG dan MID, bisa jelaskan lebih lanjut rumus (formula) yang Anda sudah coba. Setahu saya, kombinasi TERBILANG dan MID sepertinya sudah cocok dengan kebutuhan Anda. Mungkin masalahnya hanya di parameter atau hal kecil lainnya; bukan pada fungsi-fungsinya.

      Hapus
  2. sangat bermanfaat sekali, hatur nuhun bro

    BalasHapus
  3. Sangat Menarik Artikelnya. saya ada masalah sedikit kalau misalnya kita menginginkan seperti tanggal 10 januari 2014, apakah bisa tertulis Tanggal Sepuluh Bulan Januari Tahun dua Ribu Empat Belas layaknya di berita kebetulan saya lagi ada masalah di bagian itu saya menggunakan excel menulis berita acara. dan kesulitannya di bagian tanggal masih terbaca angka tanggalnya 10 bukan sepuluh dan tahun 2014 di mana saya menginginkan tertulis Dua Ribu Empat Belas,,, Terima kasih atas kesempatan yang di berikan salam sukses untuk anda

    BalasHapus
    Balasan
    1. Maksud pertanyaannya itu bagaimana merubah "10 Januari 2014" menjadi "Tanggal 10 Bulan Januari Tahun Dua Ribu Empat Belas" secara otomatis ya? Kalau iya, konsepnya mirip dengan di atas. Pertama, kita pecah dulu teks "10 Januari 2014" menjadi "10", "Januari", dan "2014". Kedua, kita buat tabel referensi untuk tanggal dan tahun (yang berisi angka dan teks yang relevan). Ketiga, kita hubungkan hasil pecahan teks dari langkah pertama ke tabel referensi dari langkah kedua. Keempat, concatenate hasil penghubungan di langkah ketiga. Konsepnya seperti itu, tapi mohon maaf saya tidak sempat buktikan langsung dengan Excel.

      Hapus
  4. Pak Amir kalau mau merubah fungsi text angka contoh '220700 ingin menjadi format tanggal lengkap 22 Juli 2000 bagai mana caranya?trims

    BalasHapus
    Balasan
    1. Idenya serupa dengan tips di atas, Mas. Coba pecah dulu text angka 220700 itu menjadi 3 bagian menggunakan fungsi LEFT, MID, atau RIGHT. Khusus untuk bulan nantinya diperlukan tabel referensi juga. Tabel referensinya mirip dengan di atas. Bedanya di kolom A diisi 00, 01, ..., s.d. 12, sementara di kolom B diisi Januari, Februari, ..., s.d. Desember.

      Semoga membantu.

      Hapus
    2. Maaf Nimbrung ... bagaimana kalo kasusnya kebalikan dari kasus diatas format 22 Juli 2000 biar menjadi 220700 (angka tahunnya 2 digit); seperti menentukan Nomor NIK berdasarkan tanggal lahir, Jenis Kelamin, dan KK diketahui ...

      Hapus
    3. Caranya hampir mirip dengan formula yang saya tulis di dalam artikel saya. Bedanya adalah tabel referensinya perlu diubah supaya angka-angkanya berisi angka 2 digit ("01", "02", "03", dst.) dan fungsi DATE-nya diubah menjadi CONCATENATE.

      Hapus
  5. mohon bantuannya p amir
    bagaimana merubah dari format tanggal/ bulan/tahun
    menjadi
    tahun-bulan-tanggal
    (masalahnya dua yaitu: balik jadi duluan tahun bulan dan tanggal
    kemuian ganti strip menjadi datar (/ ke -) sebagai pemisahnya)

    BalasHapus
    Balasan
    1. Format data awalnya itu Text atau Date? Kalau Text, perubahannya perlu menggunakan formula. Kalau Date, pengaturannya bisa dilakukan lewat pengaturan format saja.

      Hapus
  6. bagaimana cara merubah format tanggal(tex)ke format tanggal (date) dengan dasar tex seprti ini: 2000-12-06 format (tex)menjadi 06 Desember 2000 format (date)

    BalasHapus
    Balasan
    1. Prinsipnya sama dengan formula di atas. Kita perlu mengambil bagian tanggal, bulan, dan tahun dari teks untuk diubah menjadi format date (menggunakan fungsi DATE). Kalau format date sudah bisa diperoleh, kita bisa mengatur lebih lanjut lewat fitur formatting (tanpa fungsi/formula).

      Hapus
  7. Pa Amir, mohon bantuannya.
    1. Didalam daftar data text saya berbentuk dd/mm/yyyy, saya ingin merubahnya menjadi format date
    2. Sebahagian data tersebut terbaca sebagai format date, tetapi butuh koreksi
    Contoh Date 12/8/2015 terbaca oleh windows sebagai 8 Desember 2015 yang mana seharusnya 12 Agustus 2015.

    Maaf Pak Amir, mohon dibantu karena data saya ribuan, saya ingin cara yang sedikit praktis ketimbang merubah 1-1 secara manual..

    Sebelumnya saya ucapkan terima kasih

    BalasHapus
    Balasan
    1. Menggunakan M.Office Pro Plus 2010

      Hapus
    2. Pak Amir, mirip dengan kasus diatas poin 2, hanya pakai waktu, contohnya 12/8/2015 8:45:00. Terbaca 8 Desember 2015 8:45:00, seharusnya 12 Agustus 2015 8:45:00. Mohon dibantu Pak. Trims

      Hapus
    3. Untuk kasus yang hanya tanggal saja, coba gunakan rumus ini:

      DATE(RIGHT(A2,4), MID(A2, FIND("/", A2)+1, FIND("/", SUBSTITUTE(A2, "/", "-", 1))-FIND("/", A2)-1), LEFT(A2,FIND("/", A2)-1))

      Rumus di atas mengasumsikan bahwa data asli ada di cell A2. Silakan disesuaikan seperlunya.

      Idenya adalah mengubah teks itu menjadi tanggal menggunakan fungsi DATE dengan parameter berikut:
      1. Tahun = RIGHT(A2,4)
      2. Bulan = MID(A2, FIND("/", A2)+1, FIND("/", SUBSTITUTE(A2, "/", "-", 1))-FIND("/", A2)-1)
      3. Tanggal = LEFT(A2,FIND("/", A2)-1)

      Silakan dicoba.

      Hapus
    4. Untuk kasus yang juga mencantumkan waktu, saya usulkan untuk mengubah teksnya menjadi teks dengan format mm/dd/yyyy dulu (tidak langsung menggunakan fungsi DATE). Setelah itu, konversi ke format DATE bisa langsung dilakukan.

      Hapus
    5. Cara yang lebih sederhana (tidak melibatkan rumus) adalah dengan terlebih dahulu mengubah pengaturan "Region" di komputer Anda melalui Control Panel. Kalau bagian itu sudah diubah menjadi Bahasa Indonesia (atau Indonesian), masalah di atas tidak akan muncul saat kita menggunakan Excel.

      Hapus
    6. Formulanya pas, terima kasih Pak

      Hapus
  8. Pak Amir mohon bantuannya,

    1.di excel saya ada data 19560629
    2.kemudian coba saya pisah dengan right,mid left dengan masing - masing kolom yang berbeda.
    3.saya coba gabungkan menggunakan formula Date tapi ternyata hasilnya beda.

    mohon bantuannya untuk cara pemecahannya. terima kasih

    BalasHapus
    Balasan
    1. Untuk bisa membantu, saya perlu tahu formula yang dipakai dan hasilnya. Kalau hanya penjelasan di atas, saya tidak bisa menentukan masalahnya.

      Hapus
  9. Mohon bantuannya jika saya ada data format general misal
    22-01-2018 ubah ke format tanggal

    BalasHapus
    Balasan
    1. Dengan cara di atas seharusnya masih bisa, Mas. Kalau memang tidak bisa, kendalanya di mana? Monggo di-share.

      Hapus
  10. walah, akhirnya ketemu juga tutorialnya. Makasih gan, bisa :D

    BalasHapus
  11. saya tidak bisa merubah tanggal menjadi text pada exel apa penyebabnya master

    BalasHapus

Catatan: Hanya anggota dari blog ini yang dapat mengirim komentar.