Mengubah text menjadi Date/Tanggal

Excel memiliki kemampuan untuk mengubah tanggal menjadi text atau sebaliknya. Perlu diingat bahwa tanggal sebenarnya adalah angka yang menunjukkan jumlah hari setelah tanggal 1 Januari 1900.

Jadi kalau tanggal 9 Agustus 2014 sebenarnya adalah 41860, untuk mengetahui apakah suatu cell berisi tanggal atau Text yang berformat tanggal adalah melakukan operasi matematika(tambah/kurang/kali,dll) ataupun kita foemat cell tersebut menjadi format tanggal, jika berubah menjadi format yang kita inginkan maka berarti itu adalah Date/tanggal

Misalnya kita punya range yang berisi angka dan Text yang berformat Tanggal, kita coba format cell tersebut

ConvertDate01

Maka range yang berisi date akan berubah formatnya sedangnkan yang text akan tetap

ConvertDate02

Misalnya kita mendapatkan data dari orang lain berupa text  berformat tanggal (biasanya dari Text report/pdf) dan ingin mengubahnya menjadi tanggal agar bisa kita manipulasi (filter/sort/dll)?

1. Gunakan Text to Column

  • Kita block dulu range yang berisi text lalu klik Ribbon Data -> Text to Column
  • karena kita hanya mau convert artinya tidak motong2 cell maka kita bisa pilih fixed/delimited
  • ConvertDate03
  • Yang penting pastikan bahwa hasilnya hanya 1 kolom
  • Jika kita pake delimiter pastikan bahwa delimiter bukan spasi, karena akan terpotong jadi 3
  • Kalo pake Fixed, buang semua auto kolom yang dibuat oleh excel sehingga hilang semua
  • Lalu pilih Next
  • Lalu pilih Formatnya menjadi Date, format YMD
  • ConvertDat04
  • Ini hasilnya
  • ConvertDate05

Dengan Text to format maka Text akan diconversi dengan mudah namun ini ada kelemahannya yaitu hanya akan berhasil jika format text sama dengan format computer

Ini adalah format region kita di PC (bisa kita lihat di Control Panel -> Region)

ConvertDate06

Bagaimana jika ternyata text yang akan kita ubah memiliki format English misalnya. Maka  hasilnya akan seperti ini

ConvertDate08

Oleh karena itu kita harus menggunakan cara kedua yaitu dengan Formula

2. Menggunakan Formula

  • Mengubah text menjadi Date dilakukan dengan function Date
  • Syntax : DATE(year,Month,Day)
  • Year,month,day bisa berupa angka atau text yang mirip angka, misalnya 2014 atau "2014"
  • Untuk memotong2 text menjadi year/month/date bisa kita gunakan fungsi LET,MID, atau RIGHT jika textnya bentuknya sama, jika berbeda2 kita gunakan Text to Column
  • Lihat gambar dibawah ini
  • ConvertDate09
  • Kita gunakan fungsi LEFT untuk mengambil tanggal
  • =LEFT(B67,2)
  • Kita gunakan fungsi MID, untuk mengambil bulan
  • =MID(B67,4,3)
  • Kita gunakan fungsi RIGHT untuk mengambil tahun
  • =RIGHT(B67,4)
  • Lalu kita gabung dengan fungsi DATE
  • =DATE(E67,D67,C67)
  • Akan menghasilkan error #VALUE!
  • Hal ini disebabkan fungsi Date hanya menerima argumen berupa angka atau text yang mirip angka, jadi month yang berisi "jan", "feb", dst buka berupa bulan. Harus diubah dulu menjadi angka.
  • Jan ->1, Feb ->2, dst
  • Kita gunakan saja fungsi MATCH
  • Berikut rumusnya
  • =MATCH(D67,{"Jan","Feb","Mar","Apr","Mei","Jun", "Jul","Agu","Sep","Okt","Nov","Des"},FALSE)
  • Fungsi match akan menghasilkan posisi dimana kata yang dicari ada pada daftar, disini misalnya D67 yang berisi "Jan" akan diketemukan di posisi 1 pada daftar {"Jan","Feb","Mar","Apr","Mei","Jun", "Jul","Agu","Sep","Okt","Nov","Des"}
  • Baru kita gunakan rumus DATE dengan argument yang baru
  • =DATE(E67,F67,C67)

Catatan :

  • Jika kita mendapatkan text berformat Indonesia sementara PC kita berformat English maka rumus yang kita pakai adalah
  • =MATCH(D67,{"Jan" , "Feb" , "Mar" , "Apr" , "Mei" , "Jun" , "Jul" , "Agu" , "Sep" , "Okt" , "Nov" , "Des"},FALSE)
  • Jika format panjang bahasa indonesia
  • =MATCH(D67,{"Januari" , "Februari" , "Maret" , "April" , "Mei" , "Juni" , "Juli" , "Agustus" , "September" , "Oktober" , "November" , "Desember"},FALSE)
  • Format Panjang Bahasa Inggris
  • =MATCH(D67,{"January" , "February" , "March" , "April" , "May" , "June" , "July" , "August" , "September" , "October" , "November" , "December"},FALSE)
  • Jika format panjang maka kita harus menggunakan fungsi search/find untuk memisahkan year/month/day karena panjang bulan berbeda2
  • baca
  • Complex Formula : Cari Kata ke-N
  • atau
  • http://eoditor.com/tag/complex-formula/
  • Atau pisahin aja menggunakan Text to Column pada ribbon Data

3. Ubah PC ke Region yang sesuai lalu convert

  • Bagaimana jika mendapatkan text seperti sbb :
  • ConvertDate10
  • Sementara format region kita adalah Indonesia?
  • Gampang aja
  • Kita ubah dulu ke region English
  • Kita gunakan Text To Column
  • Pilih YMD
  • Balikin lagi ke region asal

4.  Gunakan e-Audit Utilities

e-Audit utilities memiliki fungsi meng-Convert text berbagai format menjadi tanggal dengan mudah

Baca : http://eoditor.com/e-audit-utilities-add-in/range/convert-text2-date/

Bookmark the permalink.

Leave a Reply