Formula generik
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Ringkasan
Untuk menukar teks dalam format tarikh yang tidak dikenali menjadi tarikh Excel yang betul, anda boleh menguraikan teks dan menyusun tarikh yang tepat dengan formula berdasarkan beberapa fungsi: DATE, LEFT, MID, dan RIGHT. Dalam contoh yang ditunjukkan, formula dalam C6 adalah:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Rumus ini mengekstrak nilai tahun, bulan, dan hari secara berasingan, dan menggunakan fungsi DATE untuk memasangkannya ke tarikh 24 Oktober 2000.
Latar belakang
Apabila anda bekerja dengan data dari sistem lain, anda mungkin menghadapi situasi di mana tarikh tidak dikenali dengan betul oleh Excel, yang sebaliknya memperlakukan tarikh seperti teks. Contohnya, anda mungkin mempunyai nilai teks seperti ini:
| Teks | Tarikh diwakili |
| 20001024 | 24 Oktober 2000 |
| 20050701 | 1 Julai 20115 |
| 19980424 | 24 April 1998 |
| 28.02.2014 | 28 Februari 2014 |
Apabila Excel telah menilai nilai tarikh sebagai teks, satu pilihan adalah menggunakan formula untuk menguraikan teks ke dalam komponennya (tahun, bulan, hari) dan menggunakannya untuk membuat tarikh dengan fungsi DATE. Seperti yang dinyatakan di atas, saya mengesyorkan anda mencuba penyelesaian di bawah (tambah sifar dan menggunakan teks ke lajur) sebelum anda menggunakan formula. Kedua-dua penyelesaian lebih pantas dan memerlukan sedikit usaha.
Penjelasan
Fungsi DATE membuat tarikh yang sah menggunakan tiga argumen: tahun, bulan, dan hari:
=DATE(year,month,day)
Dalam sel C6, kami menggunakan fungsi KIRI, MID, dan KANAN untuk mengekstrak setiap komponen ini dari rentetan teks, dan memasukkan hasilnya ke fungsi DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Fungsi KIRI mengekstrak 4 aksara paling kiri selama setahun, fungsi MID mengekstrak aksara pada kedudukan 5-6 untuk bulan, dan fungsi KANAN mengekstrak 2 aksara paling kanan sebagai hari. Setiap hasil dikembalikan terus ke fungsi DATE. Hasil akhirnya adalah tarikh Excel yang tepat yang boleh diformat mengikut keinginan anda.
Pendekatan ini dapat disesuaikan mengikut keperluan. Contohnya, format tarikh yang tidak dikenali pada baris 8 adalah dd.mm.yyyy dan formula dalam C8 adalah:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Teks bentuk panjang
Kadang-kadang anda mungkin mempunyai tarikh dalam bentuk yang lebih panjang seperti "11 Apr 2020 08:43:13" yang tidak dikenali oleh Excel dengan betul. Dalam kes ini, anda mungkin dapat menyesuaikan rentetan dengan cara yang membolehkan Excel mengenali tarikh dengan betul dengan fungsi SUBSTITUTE. Rumus di bawah ini menggantikan contoh kedua spasi ("") dengan koma dan spasi (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Sebaik sahaja kita menambahkan koma setelah nama bulan, Excel akan memahami tarikhnya, tetapi masih memerlukan sedikit "tendangan". Itulah sebabnya kita menambah sifar pada akhir. Operasi matematik menyebabkan Excel mencuba dan menukar rentetan menjadi nombor. Apabila berjaya, ini akan menghasilkan tarikh Excel yang sah. Perhatikan bahawa anda mungkin perlu menerapkan pemformatan nombor tarikh untuk memaparkan tarikh dengan betul.
Tanpa formula
Sebelum anda menggunakan formula untuk menguraikan secara manual dan membina tarikh dari teks, cubalah salah satu pembaikan di bawah. Pilihan pertama menggunakan operasi matematik untuk "mendorong" Excel sedikit dan memaksanya untuk mencuba dan menilai teks sebagai angka. Oleh kerana tarikh Excel adalah nombor sebenarnya, ini sering kali boleh dilakukan. Anda mungkin perlu menggunakan format tarikh jika operasi berjaya.
Tambahkan sifar untuk menetapkan tarikh
Kadang kala, anda akan menemui tarikh dalam format teks yang harus dikenali oleh Excel. Dalam kes ini, anda mungkin dapat memaksa Excel untuk menukar nilai teks menjadi tarikh dengan menambahkan sifar ke nilai. Apabila anda menambah sifar, Excel akan berusaha memaksa nilai teks menjadi nombor. Oleh kerana tarikh hanya angka, trik ini adalah kaedah terbaik untuk menukar tarikh dalam format teks yang semestinya difahami oleh Excel.
Untuk menukar tarikh dengan menambahkan sifar, cuba Tampal Istimewa:
- Masukkan sifar (0) dalam sel yang tidak digunakan dan salin ke papan keratan
- Pilih tarikh yang bermasalah
- Tampal Istimewa> Nilai> Tambah
- Terapkan format tarikh (jika diperlukan)
Anda juga boleh menambahkan sifar dalam formula seperti ini:
=A1+0
di mana A1 mengandungi tarikh yang tidak dikenali.
Teks ke lajur untuk menetapkan tarikh
Cara lain untuk membuat Excel mengenali tarikh adalah dengan menggunakan Ciri Teks ke Lajur:
Pilih lajur tarikh, kemudian cuba Data> Teks ke lajur> Tetap> Selesai
Sekiranya Excel mengenali tarikhnya, ia akan memperbaikinya dalam satu langkah.
