Formula umum
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Ringkasan
Untuk mengonversi teks dalam format tanggal yang tidak dikenal menjadi tanggal Excel yang tepat, Anda dapat mengurai teks dan menyusun tanggal yang tepat dengan rumus berdasarkan beberapa fungsi: DATE, LEFT, MID, dan RIGHT. Dalam contoh yang ditunjukkan, rumus di C6 adalah:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Rumus ini mengekstrak nilai tahun, bulan, dan hari secara terpisah, dan menggunakan fungsi DATE untuk menyusunnya menjadi tanggal 24 Oktober 2000.
Latar Belakang
Saat Anda bekerja dengan data dari sistem lain, Anda mungkin mengalami situasi di mana tanggal tidak dikenali dengan benar oleh Excel, yang sebaliknya memperlakukan tanggal seperti teks. Misalnya, Anda mungkin memiliki nilai teks seperti ini:
| Teks | Tanggal diwakili |
| 20001024 | 24 Oktober 2000 |
| 20050701 | 1 Juli 20115 |
| 19980424 | 24 April 1998 |
| 28.02.2014 | 28 Februari 2014 |
Saat Excel telah mengevaluasi nilai tanggal sebagai teks, salah satu opsinya adalah menggunakan rumus untuk mengurai teks menjadi komponennya (tahun, bulan, hari) dan menggunakannya untuk membuat tanggal dengan fungsi DATE. Seperti disebutkan di atas, saya sarankan Anda terlebih dahulu mencoba solusi di bawah ini (menambahkan nol dan menggunakan teks ke kolom) sebelum Anda menggunakan rumus. Kedua solusi tersebut lebih cepat dan membutuhkan lebih sedikit upaya.
Penjelasan
Fungsi DATE membuat tanggal yang valid menggunakan tiga argumen: tahun, bulan, dan hari:
=DATE(year,month,day)
Di sel C6, kami menggunakan fungsi LEFT, MID, dan RIGHT untuk mengekstrak masing-masing komponen ini dari string teks, dan memasukkan hasilnya ke dalam fungsi DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Fungsi KIRI mengekstrak 4 karakter paling kiri untuk tahun, fungsi MID mengekstrak karakter di posisi 5-6 untuk bulan, dan fungsi KANAN mengekstrak 2 karakter paling kanan sebagai hari. Setiap hasil dikembalikan langsung ke fungsi DATE. Hasil akhirnya adalah tanggal Excel yang tepat yang dapat diformat sesuka Anda.
Pendekatan ini dapat disesuaikan sesuai kebutuhan. Misalnya, format tanggal yang tidak dikenal di baris 8 adalah dd.mm.yyyy dan rumus di C8 adalah:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Teks bentuk panjang
Terkadang Anda mungkin memiliki tanggal dalam format yang lebih panjang seperti "Apr 11 2020 08:43:13" yang tidak dikenali Excel dengan benar. Dalam kasus ini, Anda mungkin dapat menyesuaikan string dengan cara yang memungkinkan Excel mengenali tanggal dengan benar dengan fungsi SUBSTITUTE. Rumus di bawah ini menggantikan contoh spasi kedua ("") dengan koma dan spasi (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Setelah kita menambahkan koma setelah nama bulan, Excel akan memahami tanggalnya, tetapi masih perlu sedikit "tendangan". Itu sebabnya kami menambahkan nol di akhir. Operasi matematika menyebabkan Excel mencoba dan mengubah string menjadi angka. Jika berhasil, ini akan menghasilkan tanggal Excel yang valid. Perhatikan bahwa Anda mungkin perlu menerapkan format angka tanggal untuk menampilkan tanggal dengan benar.
Tanpa rumus
Sebelum Anda menggunakan rumus untuk mengurai dan menyusun tanggal dari teks secara manual, coba salah satu perbaikan di bawah ini. Opsi pertama menggunakan operasi matematika untuk "mendorong" Excel sedikit dan memaksanya untuk mencoba dan mengevaluasi teks sebagai angka. Karena tanggal Excel sebenarnya adalah angka, ini sering kali berhasil. Anda mungkin perlu menerapkan format tanggal jika operasi berhasil.
Tambahkan nol untuk menetapkan tanggal
Terkadang, Anda akan menemukan tanggal dalam format teks yang seharusnya dikenali Excel. Dalam kasus ini, Anda mungkin bisa memaksa Excel untuk mengonversi nilai teks menjadi tanggal dengan menambahkan nol ke nilainya. Saat Anda menambahkan nol, Excel akan mencoba memaksakan nilai teks ke angka. Karena tanggal hanyalah angka, trik ini adalah cara terbaik untuk mengonversi tanggal dalam format teks yang harus dipahami Excel.
Untuk mengonversi tanggal di tempat dengan menambahkan nol, coba Tempel Spesial:
- Masukkan nol (0) di sel yang tidak digunakan dan salin ke clipboard
- Pilih tanggal bermasalah
- Tempel Spesial> Nilai> Tambahkan
- Terapkan format tanggal (jika perlu)
Anda juga dapat menambahkan nol dalam rumus seperti ini:
=A1+0
dengan A1 berisi tanggal yang tidak dikenal.
Teks ke kolom untuk memperbaiki tanggal
Cara lain agar Excel mengenali tanggal adalah dengan menggunakan Fitur Teks ke Kolom:
Pilih kolom tanggal, lalu coba Data> Teks ke kolom> Diperbaiki> Selesai
Jika Excel mengenali tanggalnya, itu akan memperbaiki semuanya dalam satu langkah.
