Excel mengonversi teks ke waktu

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

Tingkat keahlian. Intermediat

Tantangan Pembersihan Data

Posting 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 formula

Ada 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 Excel

Unduh 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 Teks

Dalam 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 rumus

Rumus untuk mengekstrak setiap kenaikan waktu terlihat seperti berikut

=IFERROR(VALUE(MID($C2,MAX(SEARCH(E$1,$C2)-3,1),2)),0)

Video tutorial

Dalam video saya menjelaskan setiap komponen dan memandu cara menulis rumus

Tonton di YouTube & Berlangganan ke Saluran kami

Berikut adalah penjelasan singkat tentang apa yang dilakukan setiap fungsi dalam rumus

• SEARCH - temukan teks dalam string teks dan kembalikan nomor karakter awal
• MID – kembalikan/ekstrak sebagian teks berdasarkan nomor karakter awal dan panjang karakter
• MAX – kembalikan 1 jika SEARCH mengembalikan nol untuk karakter awal. Digunakan sebagai pengganti fungsi IF
• VALUE – mengonversi teks yang dikembalikan oleh MID ke nilai numerik
• IFERROR – menangani error yang dikembalikan oleh SEARCH jika find_text tidak ditemukan. Kembalikan nol untuk menunjukkan bahwa periode waktu (jam, menit, detik) tidak ada dalam teks

Solusi #2. Fungsi SUMPRODUK

Solusi 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 rumus

Rumusnya 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. 👍

Kesimpulan

Ada 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 suka

Referensi Seluruh Kolom dapat membuat Anda MASALAH dengan Excel

Tantangan Rumus Excel. Plat Genap atau Ganjil

Cara Memperbaiki dan Mencegah Kesalahan #VALUE dengan VLOOKUP

Kapan menggunakan VLOOKUP alih-alih XLOOKUP

kata 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
Merasa terhormat

kata Peter .

Apakah ada cara untuk melakukan sebaliknya?
Mengonversi Nilai Waktu menjadi Teks

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
54m2s
18h3m12s
1h34m2s

Fungsi MAX tampaknya tidak berfungsi dengan benar

• kata Jay .

  Ada keberuntungan?

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
32m 24d 00. 32. 24
4m 59s 00. 00. 59
1j 51jt 00. 51. 00
8m 52s 00. 00. 52
1m 8s 00. 00. 00
1j 23jt 00. 23. 00

Bagaimana saya memperbaikinya untuk menempatkan mereka di tempat yang tepat?

kata Kiran .

Saya juga punya masalah serupa,

28 m 42 detik
1 jam 29 menit
29 m 5 detik
24 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