Intinya. Pelajari cara mengonversi waktu yang disimpan sebagai teks [## jam ## menit ## detik] menjadi nilai waktu [jam. mm. ss] yang dapat digunakan untuk perhitungan dan analisis data di Excel Show
Tingkat keahlian. Intermediat Tantangan Pembersihan DataPosting ini adalah yang pertama dari rangkaian solusi untuk tantangan pembersihan data yang saya sajikan di posting sebelumnya. Tantangannya adalah menggunakan alat apa pun di Excel untuk mengonversi waktu/durasi teks yang disimpan menjadi nilai numerik yang dapat diformat sebagai waktu Kumpulan data asli berisi kolom waktu/durasi teks yang dalam format berikut 1 hour 27 minutes 15 seconds Kita perlu mengubahnya menjadi angka di Excel dengan format waktu h. mm. ss yang terlihat seperti berikut 1:27:15_ Solusi berbasis formulaAda banyak sekali solusi luar biasa yang dikirimkan pada postingan asli dan video YouTube. Sekali lagi terima kasih jika Anda mengirimkan solusi Solusi paling populer menggunakan fungsi dan rumus untuk mengekstrak nilai waktu dari teks, dan mengonversinya menjadi waktu Di postingan berikutnya kita akan melihat cara menggunakan Power Query dan VBA UDF untuk melakukan konversi ini Unduh File ExcelUnduh contoh file Excel untuk mengikuti Solusi-Teks-ke-Waktu-Tantangan-Formula. xlsxDownload File tersebut menggunakan Tabel Excel. Lihat posting dan video saya tentang panduan pemula untuk Tabel Excel jika Anda belum terbiasa dengan fitur Excel yang luar biasa ini Solusi #1. Fungsi TeksDalam video di bawah ini saya membahas cara mengatasi tantangan dengan fungsi teks. Fungsi teks (MID dan SEARCH) digunakan untuk menemukan dan mengekstrak angka untuk setiap periode waktu (jam, menit, detik) Penjelasan rumusRumus untuk mengekstrak setiap kenaikan waktu terlihat seperti berikut =IFERROR(VALUE(MID($C2,MAX(SEARCH(E$1,$C2)-3,1),2)),0) Video tutorialDalam video saya menjelaskan setiap komponen dan memandu cara menulis rumus Tonton di YouTube & Berlangganan ke Saluran kamiBerikut adalah penjelasan singkat tentang apa yang dilakukan setiap fungsi dalam rumus
Solusi #2. Fungsi SUMPRODUKSolusi selanjutnya menggunakan fungsi SUMPRODUCT untuk mengubah semua periode waktu menjadi detik. Terima kasih kepada Laura dan Wong untuk berbagi teknik ini. Wong memiliki artikel lengkap tentang formula ini di wmfexcel. com Tonton di YouTube dan berikan jempol Penjelasan rumusRumusnya menggunakan teknik ekstraksi teks dasar yang sama dari solusi #1 di atas Namun, ini juga menggunakan array dalam rumus SUMPRODUCT. Hal ini memungkinkan kita melakukan ekstraksi untuk setiap periode waktu dalam satu fungsi SEARCH =SUMPRODUCT(VALUE(IFERROR(MID(0&C2,SEARCH({"h","m","sec"},0&C2)-3,2),{0,0,0})),{3600,60,1})/86400 {“h”,”m”,”sec”} adalah larik, atau daftar nilai, yang digunakan dalam argumen find_text dari SEARCH. Ini mengembalikan hasil pencarian dalam array kembali ke fungsi SUMPRODUCT Jadi waktu teks 10 jam 39 menit 40 detik akan mengembalikan larik berikut ke bagian MID(SEARCH()) dari rumus {10,39,40}_ Anda dapat menganggap fungsi SEARCH sebagai menghitung tiga waktu terpisah untuk mengembalikan hasil tersebut Array kedua di SUMPRODUCT adalah jumlah detik dalam setiap periode waktu {jam, menit, detik} {3600,60,1} Ada 3.600 detik dalam satu jam (60*60), 60 detik dalam satu menit, dan 1 detik dalam satu detik SUMPRODUCT mengalikan masing-masing nilai dan kemudian menjumlahkannya (10*3600)+(39*60)+(40*1)_ Ini mengembalikan jumlah total detik. Angka itu dibagi dengan jumlah detik dalam sehari 86.400 = (24*60*60) Hasilnya adalah angka desimal atau pecahan sepanjang hari. Setiap hari Excel diwakili oleh bilangan bulat. Lihat artikel saya tentang tipe data tanggal dan sistem kalender di Excel untuk mempelajari lebih lanjut Bagaimana jika durasinya lebih dari 24 jam?Keuntungan dari solusi SUMPRODUCT adalah dapat menangani waktu lebih dari 24 jam. Karena mengembalikan jumlah detik, lebih dari 86.400 detik masih akan mengembalikan angka desimal Kita dapat menggunakan format berikut untuk menampilkan jumlah hari, jam, menit, dan detik d.h:mm:ss Rumus SUMPRODUCT adalah solusi yang sangat kuat, dan sangat bagus. 👍 KesimpulanAda banyak cara berbeda untuk menyelesaikan masalah ini dengan rumus. Dalam posting ini kami melihat dua formula berbeda untuk mengekstrak kenaikan waktu dan mengubahnya menjadi nilai waktu Di posting berikutnya kita akan melihat solusi dengan Power Query dan VBA UDF (User Defined Functions) Silakan tinggalkan komentar di bawah ini dengan pertanyaan atau saran. Terima kasih. 🙂 Sebelumnya 5 Tips Pengeditan Rumus untuk Excel Berikutnya Cara Menangani Kesalahan Rumus Vlookup Anda mungkin juga sukaReferensi Seluruh Kolom dapat membuat Anda MASALAH dengan ExcelTantangan Rumus Excel. Plat Genap atau GanjilCara Memperbaiki dan Mencegah Kesalahan #VALUE dengan VLOOKUPKapan menggunakan VLOOKUP alih-alih XLOOKUPkata David N . Harap diperhatikan bahwa solusi SUMPRODUCT rentan terhadap masalah pembulatan Excel untuk desimal yang panjang, sehingga jawaban yang benar di mata kita kadang-kadang bukan "jawaban yang benar" di Excel. 4. 43. 23 hasil untuk baris 979 adalah contohnya. Untuk detail lebih lanjut, lihat balasan saya untuk Toni di halaman 2 komentar untuk posting asli, dan perhatikan bahwa variasi dari Laura dan Wong ini hanya menghasilkan 8 nyaris meleset, bukan 14 dari pendekatan Toni. MF berkata. Terima kasih telah menyebutkan kata Peter . Apakah ada cara untuk melakukan sebaliknya? misalnya. mengkonversi 1. 15. 10 hingga 1 jam 15 menit 10 detik kata Mawar . Bagaimana saya bisa mendapatkan waktu rata-rata di pivot? K . Apa versi fungsi Excel ini? kata Chris . Apa yang harus saya lakukan untuk menggunakan rumus ini dengan nilai seperti berikut? 2h43m3s Fungsi MAX tampaknya tidak berfungsi dengan benar
kata Sacheen Richards . Hai, Tolong bantu, saya sedang mengetik rumus =SUMPRODUCT(IFERROR(VALUE(MID(0&$N2,SEARCH({“h”,”m”,”s”},0&$N2)-3,3)), Jawaban saya adalah Bagaimana saya memperbaikinya untuk menempatkan mereka di tempat yang tepat? kata Kiran . Saya juga punya masalah serupa, 28 m 42 detik Apakah ada solusi untuk ini? ptm mengatakan. Hai, sangat terlambat ke pesta. Ini berfungsi untuk saya tetapi saya perhatikan bahwa beberapa bidang saya memiliki angka seperti 123 menit. Saat fungsi pertama (=SUMPRODUCT(VALUE(IFERROR(MID(0&C2,SEARCH({“h”,”m”,”sec”},0&C2)-3,2),{0,0,0})), . 23. 00 bukannya 2. 03. 00. Apakah ada cara mudah untuk memperbaikinya? Bagaimana cara mengonversi teks menjadi tanggal dan waktu di Excel?Cara mengubah teks menjadi tanggal di Excel dengan cara mudah . Pilih sel dengan string teks dan klik tombol Text to Date Tentukan urutan tanggal (hari, bulan, dan tahun) di sel yang dipilih Pilih apakah akan menyertakan atau tidak menyertakan waktu dalam tanggal yang dikonversi Klik Konversi Bagaimana cara mengonversi teks ke HH MM di Excel?Pilih sel yang akan menempatkan hasil teks, ketik rumus ini =TEXT(A1,"hh. mm. ss AM/PM") , tekan tombol Enter. Dan jika perlu, seret gagang isian ke bawah untuk menerapkan rumus ini ke sel di bawah ini.
Bagaimana cara mengubah teks menjadi jam dan menit?Untuk mengubah waktu menjadi jam, kalikan waktu dengan 24, yang merupakan jumlah jam dalam sehari. Untuk mengubah waktu menjadi menit, kalikan waktu dengan 1440, yang merupakan jumlah menit dalam sehari (24*60) . Untuk mengubah waktu menjadi detik, kalikan waktu dengan 86400, yang merupakan jumlah detik dalam sehari (24*60*60 ).
Bagaimana cara mengonversi data menjadi jam di Excel?1. Anda juga dapat menggunakan rumus ini untuk mengonversi waktu menjadi jam =HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600. 2. Jika Anda ingin mengubah waktu menjadi menit desimal, gunakan ini =A1*24*60 atau =HOUR(A1) * 60 + MINUTE(A1) + SECOND(A1) / 60 |