Terdapat banyak cara untuk menukar tarikh yang diformatkan sebagai teks kepada tarikh sebenar dalam Excel dan tutorial ini bertujuan untuk meneroka kesemuanya.
Apabila anda mengimport data ke dalam Excel, ia mungkin datang dalam pelbagai bentuk yang tidak dikenali oleh Excel. Kadangkala apabila tarikh diimport daripada sumber luaran, tarikh tersebut akan diformatkan sebagai teks.
Penting juga untuk mengetahui bahawa Excel menggunakan format tarikh berdasarkan tetapan wilayah sistem anda. Jadi, apabila anda mengimport data ke dalam Excel yang datang dari negara lain, Excel mungkin tidak mengenalinya dan menyimpannya sebagai entri teks.
Jika anda menghadapi isu ini, terdapat banyak cara yang boleh anda gunakan untuk menukar teks kepada tarikh dalam Excel dan kami akan membincangkan semuanya dalam tutorial ini.
Kaedah untuk Menukar Teks kepada Tarikh
Jika lembaran kerja anda mengandungi tarikh yang diformatkan sebagai teks, bukannya tarikh sebenar, ia tidak boleh digunakan dalam sebarang pengiraan. Jadi, anda perlu menukarnya kembali kepada tarikh sebenar.
Jika anda melihat tarikh anda dijajarkan ke kiri itu bermakna tarikh tersebut diformatkan sebagai teks kerana teks dijajarkan ke kiri secara lalai. Dan nombor dan tarikh sentiasa diselaraskan ke kanan.
Terdapat beberapa cara berbeza anda boleh menukar teks kepada tarikh dalam Excel, ia adalah:
- Pilihan Semakan Ralat
- Ciri Teks ke Lajur Excel
- Cari & Ganti
- Tampal alat Khas
- Formula dan fungsi Excel
Tukar Teks kepada Tarikh Menggunakan Pilihan Semakan Ralat
Excel mempunyai ciri semakan ralat terbina yang mengesan beberapa ralat yang jelas dalam data anda. Jika ia menemui sebarang ralat, ia akan menunjukkan kepada anda segitiga hijau kecil (penunjuk ralat) di penjuru kiri sebelah atas sel, yang mempunyai ralat. Jika anda memilih sel itu, tanda berhati-hati dengan tanda seru kuning akan muncul. Apabila anda mengalihkan kursor anda ke atas tanda itu, dan Excel akan memberitahu anda tentang kemungkinan isu dengan sel itu.
Contohnya, apabila anda memasukkan tahun dalam format dua digit dalam tarikh anda, Excel menganggap tarikh itu sebagai teks dan menyimpannya sebagai teks. Dan jika anda memilih sel itu, tanda seru akan muncul dengan amaran: 'Sel ini mengandungi rentetan tarikh yang diwakili dengan hanya 2 digit tahun'.
Jika sel anda menunjukkan penunjuk ralat itu, klik pada tanda seru, dan ia akan memaparkan beberapa pilihan untuk menukar tarikh yang diformatkan sebagai teks kepada tarikh sebenar. Excel akan menunjukkan kepada anda pilihan untuk menukarnya kepada 19XX atau 20XX (19XX untuk 1915, 20XX untuk 2015). Pilih pilihan yang sesuai.
Kemudian, teks akan ditukar kepada format tarikh yang betul.
Cara Mendayakan Pilihan Semakan Ralat dalam Excel
Biasanya, pilihan Semakan Ralat dihidupkan dalam Excel secara lalai. Jika ciri semakan ralat tidak berfungsi untuk anda, anda perlu mendayakan Semakan Ralat dalam Excel.
Untuk melakukan ini, klik pada tab 'Fail' dan pilih 'Pilihan' di panel kiri.
Pada tetingkap Excel Options, klik 'Formula' di panel kiri, dan dalam panel sebelah kanan, dayakan 'Dayakan pemeriksaan ralat latar belakang' di bawah bahagian Penyemakan Ralat. Dan tandakan 'Sel yang mengandungi tahun diwakili sebagai 2 digit' dalam bahagian peraturan menyemak ralat.
Tukar Teks kepada Tarikh Menggunakan Teks Excel kepada Ciri Lajur
Text to Column ialah ciri hebat dalam Excel yang membolehkan anda membahagikan data kepada berbilang lajur, dan ia juga merupakan alat yang berkuasa untuk menukar nilai teks kepada nilai tarikh. Kaedah ini mengenali beberapa format data yang berbeza dan menukarkannya kepada format tarikh yang betul.
Menukar Rentetan Teks Mudah kepada Tarikh
Katakan tarikh anda diformatkan dalam rentetan teks seperti ini:
Anda boleh menggunakan wizard Text to Columns untuk memformat semula semuanya kembali ke tarikh dengan pantas.
Mula-mula, pilih julat entri teks yang ingin anda tukar kepada tarikh. Kemudian, pergi ke tab 'Data' dalam Reben dan klik pilihan 'Teks ke Lajur' dalam kumpulan Alat Data.
Wizard Teks ke Lajur akan muncul. Pada langkah 1 wizard Teks ke lajur, pilih pilihan 'Terhad' di bawah jenis data Asal dan klik 'Seterusnya'.
Dalam langkah 2, nyahtanda semua kotak 'Pembatas' dan klik Seterusnya.
Dalam langkah terakhir wizard, pilih 'Tarikh' di bawah format data Lajur dan pilih format tarikh anda daripada senarai juntai bawah 'Tarikh' seterusnya, dan klik butang 'Selesai'. Dalam kes kami, kami menukar tarikh teks yang diwakili sebagai "01 02 1995" (hari bulan tahun), jadi kami memilih 'DMY' daripada senarai lungsur turun 'Tarikh:'.
Kini, Excel menukar tarikh teks anda kepada tarikh sebenar dan memaparkannya sejajar kanan dalam sel.
Nota: Sebelum anda mula menggunakan ciri Teks ke Lajur, pastikan semua rentetan teks anda dalam format yang sama, jika tidak teks tidak akan ditukar. Sebagai contoh, jika beberapa tarikh teks anda diformatkan seperti format bulan/hari/tahun (MDY) manakala yang lain ialah hari/bulan/tahun (DMY), dan apabila anda memilih 'DMY' dalam langkah 3, anda akan mendapat hasil yang salah. Seperti yang ditunjukkan dalam gambar di bawah.
Menukar rentetan Teks Kompleks kepada Tarikh
Ciri Teks ke Lajur berguna apabila anda ingin menukar rentetan teks kompleks kepada tarikh. Ia membolehkan anda menggunakan pembatas untuk mengenal pasti tempat data anda harus dipecah dan dipaparkan pada 2 atau lebih lajur. Dan gabungkan bahagian pecahan tarikh ke seluruh tarikh menggunakan fungsi DATE.
Contohnya, jika tarikh anda dipaparkan dalam rentetan teks berbilang bahagian, seperti ini:
Rabu, 01 Februari 2020
01 Februari 2020, 4.10 PETANG
Anda boleh menggunakan wizard Teks ke lajur untuk memisahkan maklumat hari, tarikh dan masa yang dihadkan dengan koma dan memaparkannya merentas berbilang lajur.
Mula-mula, pilih semua rentetan teks yang ingin anda tukar kepada tarikh. Klik butang ‘Teks ke Lajur’ pada tab ‘Data’. Pada langkah 1 wizard Teks ke lajur, pilih pilihan 'Terhad' di bawah jenis data Asal dan klik 'Seterusnya'.
Dalam langkah 2 wizard, pilih pembatas rentetan teks anda dan klik 'Seterusnya'. Rentetan teks contoh kami dipisahkan dengan koma dan ruang - "Isnin, 01 Februari 2015, 1:00 PTG". Kita harus memilih 'Koma' dan 'ruang' sebagai pembatas untuk memisahkan rentetan teks kepada berbilang lajur.
Pada langkah terakhir, pilih format 'Umum' untuk semua lajur dalam bahagian Pratonton Data. Tentukan tempat lajur harus dimasukkan dalam medan 'Destinasi', jika tidak, ia akan menimpa data asal. Jika anda ingin mengabaikan sebahagian daripada data asal, klik padanya dalam bahagian Pratonton Data dan pilih pilihan 'Jangan import lajur (langkau)'. Kemudian, klik ‘Selesai’.
Kini, bahagian tarikh (hari dalam minggu, bulan, tahun, masa) dibahagikan dalam lajur B, C, D, E, F dan G.
Kemudian, gabungkan bahagian tarikh bersama-sama dengan bantuan formula DATE untuk mendapatkan keseluruhan tarikh.
Sintaks fungsi Excel DATE:
=DATE(tahun, bulan, hari)Dalam contoh kami, bahagian bulan, hari dan tahun masing-masing berada dalam lajur C, D dan E.
Fungsi DATE hanya mengenali nombor bukan teks. Oleh kerana nilai bulan kami dalam lajur C ialah semua rentetan teks, kami perlu menukarnya kepada nombor. Untuk melakukan itu, anda perlu menggunakan fungsi MONTH untuk menukar nama bulan kepada nombor bulan.
Untuk menukar nama bulan kepada nombor bulan, gunakan fungsi MONTH ini di dalam fungsi DATE:
=BULAN(1&C1)Fungsi MONTH menambah 1 pada sel C2 yang mengandungi nama bulan untuk menukar nama bulan kepada nombor bulan yang sepadan.
Ini ialah fungsi DATE yang perlu kita gunakan untuk menggabungkan bahagian tarikh daripada lajur yang berbeza:
=TARIKH(E1,BULAN(1&C1),D1)
Sekarang gunakan pemegang isian di sudut bawah sel formula dan gunakan formula pada lajur.
Tukar Teks kepada Tarikh Menggunakan Kaedah Cari dan Ganti
Kaedah ini menggunakan pembatas untuk menukar format teks kepada tarikh. Jika hari, bulan dan tahun dalam tarikh anda dipisahkan oleh beberapa pembatas selain daripada sempang (-) atau sengkang (/), Excel tidak akan mengenalinya sebagai tarikh dan akan meneruskan dan menyimpannya sebagai teks.
Untuk menyelesaikan isu ini, gunakan ciri Cari dan Ganti. Dengan menukar pembatas tempoh bukan standard (.) dengan garis miring (/) atau sempang (-), Excel secara automatik akan menukar nilai kepada tarikh.
Mula-mula, pilih semua tarikh teks yang anda ingin tukar kepada tarikh. Pada tab 'Rumah', klik butang 'Cari & Pilih' di sudut paling kanan Reben dan pilih 'Ganti'. Sebagai alternatif, tekan Ctrl+H untuk membuka kotak dialog Cari dan Ganti.
Dalam kotak dialog Cari dan Ganti, taipkan pembatas yang mengandungi teks anda (dalam kes kami noktah (.) dalam medan 'Cari apa' dan garis miring ke hadapan (/) atau sempang (-) dalam medan 'Ganti dengan' Klik butang 'Ganti Semua' untuk menggantikan pembatas dan klik 'Tutup' untuk menutup tetingkap.
Kini, Excel menyedari bahawa rentetan teks anda kini adalah tarikh dan ia secara automatik memformatkannya sebagai tarikh. Tarikh anda akan diselaraskan dengan betul seperti yang ditunjukkan di bawah.
Tukar Teks kepada Tarikh Menggunakan Alat Tampal Khas
Satu lagi cara cepat dan mudah untuk menukar rentetan teks kepada tarikh ialah dengan menambah 0 pada rentetan teks menggunakan pilihan tampal khas. Menambah sifar kepada nilai menukar teks kepada nombor siri tarikh yang boleh anda formatkan sebagai tarikh.
Mula-mula, pilih sel kosong dan salinnya (pilih dan tekan Ctrl + C untuk menyalin).
Kemudian, pilih sel yang mengandungi tarikh teks yang anda ingin tukar, klik kanan dan pilih pilihan 'Tampal Khas'.
Dalam dialog Tampal Khas, pilih 'Semua' di bawah bahagian Tampal, pilih 'Tambah' di bawah bahagian Operasi, dan klik 'OK'.
Anda juga boleh melakukan operasi aritmetik lain tolak/darab/bahagi nilai dalam sel destinasi dengan nilai tampal (seperti sel darab dengan 1 atau bahagi dengan 1 atau tolak sifar).
Apabila anda memilih 'Tambah' dalam Operasi, ia menambah 'sifar' pada semua tarikh teks yang dipilih, memandangkan penambahan '0' tidak mengubah nilai, anda akan mendapat nombor siri untuk setiap tarikh. Sekarang anda hanya perlu menukar format sel.
Pilih nombor siri dan pada tab 'Home', klik pada senarai juntai bawah 'Nombor Format' dalam kumpulan Nombor. Pilih, pilihan 'Tarikh Pendek' daripada menu lungsur.
Seperti yang anda boleh lihat sekarang nombor diformatkan sebagai tarikh dan diselaraskan ke kanan.
Tukar Teks kepada Tarikh Menggunakan Formula
Terdapat dua fungsi yang digunakan terutamanya untuk menukar teks kepada tarikh: DATEVALUE dan VALUE.
Menggunakan Fungsi Excel DATEVALUE
Fungsi Excel DATEVALUE ialah salah satu cara paling mudah untuk menukar tarikh yang diwakili sebagai teks kepada nombor siri tarikh.
Sintaks fungsi DATEVALUE:
=DATEVALUE(teks_tarikh)Hujah: tarikh_teks menentukan rentetan teks yang anda ingin rahsiakan atau merujuk kepada sel yang mengandungi tarikh teks.
Formulanya:
=DATEVALUE(A1)Gambar berikut menggambarkan bagaimana fungsi DATEVALUE mengendalikan beberapa format tarikh berbeza yang disimpan sebagai teks.
Anda mendapat nombor siri tarikh, kini anda perlu menggunakan format Tarikh pada nombor ini. Untuk melakukan itu, pilih sel dengan nombor siri, kemudian pergi ke tab 'Rumah' dan pilih 'Tarikh Pendek' daripada senarai juntai bawah 'Format Nombor'.
Kini anda mempunyai tarikh terformat anda dalam lajur C.
Walaupun tiada bahagian tahun dalam tarikh teks anda (A8), DATEVALUE akan menggunakan tahun semasa daripada jam komputer anda.
Fungsi DATEVALUE hanya akan menukar nilai teks yang kelihatan seperti tarikh. Ia tidak boleh menukar teks yang menyerupai nombor hingga kini, dan ia juga tidak boleh menukar nilai nombor hingga kini, untuk itu anda memerlukan fungsi VALUE Excel.
Menggunakan Fungsi Excel VALUE
Fungsi Excel VALUE dapat menukar mana-mana rentetan teks yang menyerupai tarikh atau nombor kepada nilai berangka, jadi ia sangat membantu apabila ia datang untuk menukar sebarang nombor, bukan hanya tarikh.
Fungsi VALUE:
=VALUE(teks)teks– rentetan teks yang ingin kita tukar atau rujuk kepada sel yang mengandungi rentetan teks.
Formula contoh untuk menukar tarikh teks:
=VALUE(A1)Formula VALUE di bawah boleh menukar mana-mana rentetan teks yang kelihatan seperti tarikh kepada nombor seperti yang ditunjukkan di bawah.
Walau bagaimanapun, fungsi VALUE tidak menyokong semua jenis nilai tarikh. Sebagai contoh, jika tarikh menggunakan tempat perpuluhan (A11), ia akan mengembalikan #VALUE! ralat.
Sebaik sahaja anda mempunyai nombor siri untuk tarikh anda, anda perlu memformatkan sel dengan nombor siri tarikh untuk menjadikannya kelihatan seperti tarikh seperti yang telah kami lakukan untuk fungsi DATEVALUE. Untuk berbuat demikian, pilih nombor siri dan pilih pilihan 'Tarikh' daripada menu lungsur 'Format Nombor' dalam tab 'Rumah'.
Itu sahaja, ini adalah 5 cara berbeza anda boleh menukar tarikh yang diformatkan sebagai teks kepada tarikh dalam Excel.