Rumus Waktu Excel: Hitung, Format, dan Olah Waktu di Excel

Excel memudahkan bekerja dengan angka, tetapi mengelola waktu (menurut saya) adalah cerita yang berbeda. Bagian yang membingungkan (yang akan saya bahas di bawah) adalah bahwa Excel menyimpan dan memproses nilai waktu sebagai pecahan hari, yang dapat menghasilkan hal-hal tak terduga jika Anda belum terbiasa.

Dalam tutorial ini, saya akan memandu Anda dari membuat dan memformat nilai waktu hingga menyelesaikan masalah umum seperti durasi negatif dan kelebihan waktu. Pada akhirnya, Anda akan tahu cara membangun spreadsheet yang lebih cerdas dan andal untuk perencanaan proyek, penggajian, atau apa pun yang sedang Anda kerjakan.

Memahami Sistem Tanggal Excel

Kita harus mulai dengan sistem tanggal Excel. Excel menyimpan tanggal sebagai nomor seri berurutan, dimulai dari 1 Januari 1900, yang diwakili oleh nilai 1. Setiap hari berikutnya menambah nomor seri sebesar 1. Contohnya:

• 1 Januari 1900 = 1

• 2 Januari 1900 = 2

• 31 Desember 2023 = 45205

Nilai waktu disimpan sebagai pecahan dari satu hari. Misalnya:

• 06.00 = 0.25 (6 jam adalah 25% dari satu hari 24 jam)

• 12.00 = 0.5

• 18.00 = 0.75

Menggabungkan tanggal dan waktu sesederhana menambahkan kedua nilai tersebut. Misalnya, rumus di bawah menggabungkan tanggal dan waktu:

=A2 + TIME(10, 30, 0)

Jika A2 berisi 45205 (31 Desember 2023), hasilnya akan 45205.4375, mewakili pukul 10.30 pada 31 Desember 2023.

Cara Membuat Nilai Waktu di Excel

Mari lihat dua cara paling umum untuk membuat nilai waktu yang valid di Excel: menggunakan fungsi TIME() dan mengonversi teks dengan TIMEVALUE().

Gunakan fungsi TIME()

Ini sudah saya sebutkan sebelumnya. Fungsi TIME() memungkinkan Anda membangun nilai waktu dari komponen terpisah: jam, menit, dan detik. Berikut sintaksnya:

=TIME(hour, minute, second)

Sebagai contoh, jika Anda ingin merepresentasikan pukul 10.30, Anda dapat menulis:

=TIME(10, 30, 0)

Ini membuat nilai waktu Excel yang benar untuk pukul 10.30, yang disimpan secara internal sebagai 0,4375 (karena 10 jam 30 menit adalah 43,75% dari satu hari 24 jam).

Excel fleksibel dengan nilai yang Anda masukkan. Jika Anda melebihi batas waktu normal, Excel akan menggulung (roll over) kelebihan waktunya. Misalnya:

=TIME(27, 0, 0)

Ini mengembalikan pukul 03.00 karena 27 jam sama dengan satu hari penuh (24 jam) plus 3 jam. Begitu pula:

=TIME(10, 75, 0)

Ini ditafsirkan sebagai pukul 11.15, karena 75 menit menjadi 1 jam 15 menit.

Kasus tepi seperti ini penting dipahami, terutama jika Anda menghasilkan nilai waktu secara dinamis menggunakan rumus.

Gunakan fungsi TIMEVALUE()

Anda dapat mengonversi teks menjadi waktu dengan TIMEVALUE(). Jadi jika spreadsheet Anda memuat waktu yang ditulis sebagai teks, seperti “2:30 PM” atau “14:45”, Excel mungkin tidak langsung memperlakukannya sebagai nilai waktu. Di sinilah TIMEVALUE() berguna.

=TIMEVALUE("2:30 PM")

Ini mengonversi string teks menjadi nilai waktu Excel. Anda kini dapat menggunakannya dalam perhitungan seperti waktu lainnya.

Perlu dicatat perbedaan antara TIME() dan TIMEVALUE():

• TIME() membangun waktu dari angka terpisah (jam, menit, detik)

• TIMEVALUE() mengonversi string teks menjadi waktu

Keduanya mengembalikan nilai yang dikenali Excel sebagai waktu yang valid, yang Anda butuhkan untuk perhitungan dan pemformatan yang akurat.

Cara Menghitung Selisih Waktu

Setelah Anda memiliki nilai waktu yang valid di Excel, salah satu tugas paling umum adalah menghitung berapa lama waktu yang berlalu antara dua titik. Baik Anda mengukur lamanya shift atau menghitung waktu yang dibutuhkan untuk menyelesaikan tugas, Excel memberi Anda beberapa cara untuk mendapatkan jawabannya.

Mari telusuri pendekatan paling berguna, dari pengurangan sederhana hingga trik yang lebih lanjut.

Kurangi dua waktu secara langsung

Cara paling langsung untuk menghitung selisih waktu adalah mengurangkan waktu mulai dari waktu selesai. 

Sebagai contoh, jika sel A1 berisi pukul 10.00 dan B1 berisi pukul 13.30, rumusnya seperti ini:

=B1 - A1

Hasilnya akan 0,145833. Ini mungkin terlihat janggal pada awalnya, tetapi ingat bahwa Excel merepresentasikan waktu sebagai pecahan hari. Dalam hal ini, 0,145833 setara dengan 3 jam 30 menit.

Agar hasilnya lebih berguna, biasanya Anda ingin mengonversinya ke format yang mudah dibaca.

Konversi selisih waktu ke jam, menit, atau detik

Anda dapat mengalikan hasil pengurangan waktu untuk mengekspresikan selisihnya dalam jam, menit, atau detik. Berikut cara mengonversi selisih waktu ke jam:

=(B2 - A2) * 24

Berikut cara mengonversi selisih waktu ke menit:

=(B2 - A2) * 1440

Berikut cara mengonversi selisih waktu ke detik:

=(B2 - A2) * 86400

Rumus-rumus ini bekerja karena ada 24 jam, 1.440 menit, dan 86.400 detik dalam sehari. Jika selisih waktunya tiga setengah jam, Anda akan mendapatkan 3,5 dari rumus jam, 210 dari rumus menit, dan 12.600 dari rumus detik.

Ekstrak unit waktu tertentu

Jika Anda hanya ingin mengambil satu bagian waktu, misalnya hanya menit atau jam, Anda dapat menggunakan HOUR(). HOUR() mengembalikan bagian jam dari suatu waktu.

=HOUR(B2 - A2)

MINUTE() mengembalikan bagian menit.

=MINUTE(B2 - A2)

SECOND() mengembalikan detik.

=SECOND(B2 - A2)

Fungsi-fungsi ini berguna, tetapi akan kembali ke nol setelah 23 jam, 59 menit, atau 59 detik. Jadi jika durasi lebih dari satu hari penuh, fungsi tersebut tidak akan mencerminkan total waktu yang berlalu. Dalam kasus seperti itu, lebih baik mengalikan selisihnya atau menggunakan format kustom, yang akan kita bahas nanti.

Menambahkan nilai waktu lebih dari 24 jam

Jika Anda perlu menambahkan jam yang melebihi 24 jam, penggunaan langsung TIME() tidak akan bekerja sebagaimana mestinya. Sebagai gantinya, Anda dapat melakukan aritmetika menggunakan pecahan hari.

Berikut cara menambahkan jam lebih dari 24.

=A2 + (28/24)  ' Adds 28 hours

Berikut cara menambahkan menit lebih dari 60.

=A2 + (90/1440)  ' Adds 90 minutes

Berikut cara menambahkan detik lebih dari 60.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel menyimpan waktu sebagai desimal, di mana 1 hari sama dengan 1, sehingga membagi dengan 24, 1440, atau 86400 menyesuaikan nilai untuk merepresentasikan porsi hari.

Menangani nilai waktu negatif

Secara bawaan, jika Anda mengurangkan waktu yang lebih besar dari waktu yang lebih kecil (misalnya, 10:00 AM - 2:00 PM), Excel akan mengembalikan error atau menampilkan deretan ###.

Untuk menangani nilai waktu negatif dengan aman, gunakan fungsi MOD(). Fungsi ini secara efektif mencegah hasil negatif dengan membungkusnya menjadi waktu yang valid.

Sebagai contoh, untuk mengurangkan 3 jam dengan aman:

=MOD(A2 - TIME(3, 0, 0), 1)

Rumus ini memastikan hasilnya selalu bernilai waktu positif, mencegah error atau masalah tampilan yang tidak diinginkan.

Cara Memformat Hasil Waktu dengan Benar

Perhitungan waktu di Excel sering menghasilkan nilai desimal yang membingungkan atau hasil tak terduga, terutama saat berurusan dengan durasi lebih dari 24 jam atau nilai waktu negatif. Pemformatan yang tepat dapat mengubah keluaran mentah menjadi format yang mudah dibaca dan disajikan.

Menggunakan pemformatan waktu dasar

Jika Anda ingin menampilkan selisih waktu seperti “3:30” alih-alih desimal, gunakan fungsi TEXT().

=TEXT(B2 - A2, "h:mm")

Ini memformat hasil sebagai jam dan menit. Perlu diingat bahwa keluarannya berupa teks yang terlihat bagus tetapi Anda tidak dapat menggunakannya dalam perhitungan matematika selanjutnya kecuali dikonversi kembali.

Menerapkan format waktu kustom

Format waktu bawaan Excel menangani nilai waktu standar secara efektif, tetapi format kustom memberikan fleksibilitas lebih besar untuk menampilkan durasi, jam yang panjang, atau komponen waktu tertentu.

Untuk menerapkan format waktu kustom, ikuti langkah-langkah ini:

1. Pilih sel yang berisi nilai waktu.
2. Klik kanan dan pilih Format Cells.
3. Di tab Number, pilih Custom.
4. Masukkan kode format yang diinginkan.

Berikut beberapa format waktu kustom yang umum:

• [h]:mm: Menampilkan total jam dan menit, bahkan melampaui 24 jam. Contoh: 36:45 (36 jam 45 menit)

• d "days" h:mm:ss: Menampilkan hari, jam, menit, dan detik. Contoh: 1 days 12:30:00

• h:mm AM/PM: Menampilkan waktu dengan notasi AM/PM. Contoh: 10:30 PM

• mm:ss: Menampilkan hanya menit dan detik. Berikut contohnya: 45:15

Format kustom memungkinkan Anda menyajikan data waktu dengan cara yang lebih intuitif untuk kasus penggunaan tertentu, seperti lembar waktu atau log kerja.

Menampilkan selisih waktu yang ramah dibaca

Jika Anda ingin menampilkan selisih waktu sebagai string yang mudah dibaca, seperti “2 days, 4 hours, and 15 minutes,” Anda dapat menggabungkan beberapa fungsi dalam satu rumus.

Berikut contoh praktisnya:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

Dalam rumus ini, fungsi INT() mengekstrak jumlah hari penuh, sementara HOUR() dan MINUTE() mengambil sisa jam dan menit.

Untuk mencegah nilai nol muncul pada keluaran, Anda dapat menempatkan perhitungan ini dalam pernyataan IF(). Misalnya:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Rumus ini hanya menampilkan unit yang relevan, sehingga hasilnya lebih rapi dan mudah dibaca.

Fungsi Waktu Excel Penting yang Perlu Diketahui

Sejauh ini Anda telah melihat, Excel memiliki rangkaian fungsi waktu yang kuat. Demi kelengkapan, berikut ringkasan singkat fungsi waktu Excel terpenting.

Menggunakan fungsi TIME() untuk membuat nilai waktu

TIME() menyusun nilai waktu yang valid dari tiga komponen: jam, menit, dan detik. Fungsi ini ideal saat Anda perlu membangun nilai waktu secara terprogram.

Berikut contoh yang bagus:

=TIME(14, 30, 0)  

Di atas akan mengembalikan pukul 2.30 PM sebagai hasil dari sintaks ini.

=TIME(hour, minute, second)

Mengonversi teks menjadi waktu dengan TIMEVALUE()

Jika Anda memiliki waktu yang ditulis sebagai string teks (mis., "2:45 PM"), gunakan TIMEVALUE() untuk mengonversinya menjadi nilai waktu Excel yang tepat yang dapat digunakan dalam perhitungan.

=TIMEVALUE("2:45 PM")

Sebagai contoh, ini akan mengembalikan 0.614583 yang merupakan pecahan hari:

=TIMEVALUE("14:45")

Mendapatkan tanggal dan waktu saat ini dengan NOW()

NOW() mengembalikan tanggal dan waktu saat ini sebagai satu nilai. Nilai ini diperbarui secara otomatis setiap kali lembar kerja dihitung ulang.

=NOW()

Berikut contoh yang bagus. Jika waktu saat ini adalah pukul 3.15 PM, NOW() mungkin mengembalikan 44710.63542, di mana 44710 adalah tanggal dan .63542 mewakili waktu.

Mengambil tanggal saat ini dengan TODAY()

Jika Anda hanya memerlukan tanggal saat ini tanpa waktu, gunakan TODAY(). Fungsi ini sangat berguna untuk menghitung tenggat waktu atau umur data.

=TODAY()

Mengekstrak komponen waktu dengan HOUR(), MINUTE(), dan SECOND()

Fungsi-fungsi ini memungkinkan Anda mengisolasi bagian tertentu dari nilai waktu, sehingga berguna untuk pelaporan dan logika bersyarat.

HOUR() mengembalikan bagian jam dari nilai waktu (0–23).

=HOUR(A2)

MINUTE() mengembalikan bagian menit (0–59).

=MINUTE(A2)

SECOND() mengembalikan bagian detik (0–59).

=SECOND(A2)

Memformat nilai waktu dengan TEXT()

TEXT() mengonversi nilai waktu ke format tertentu, menjadikannya ideal untuk menampilkan durasi atau membuat string waktu kustom. Sintaks:

=TEXT(A2, "h:mm:ss AM/PM")

Menangani nilai waktu negatif dengan TEXT()

Saat mengurangkan nilai waktu, MOD() memastikan hasilnya selalu positif, mencegah error akibat nilai waktu negatif.

=MOD(A2 - B2, 1)

Membuat nilai tanggal dengan DATE()

DATE() memungkinkan Anda membuat tanggal dari komponen terpisah: tahun, bulan, dan hari. Ini berguna untuk menggabungkan tanggal dan waktu menjadi satu nilai.

=DATE(year, month, day)

Berikut contoh yang bagus:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Yang mengembalikan 12/31/2023 2:30 PM.

Menghasilkan hari kerja dengan NETWORKDAYS()

NETWORKDAYS() menghitung jumlah hari kerja antara dua tanggal, tidak termasuk akhir pekan dan hari libur yang ditentukan secara opsional. Fungsi ini ideal untuk menghitung durasi proyek atau periode penggajian.

=NETWORKDAYS(start_date, end_date, [holidays])

Sebagai contoh:

=NETWORKDAYS(A2, B2, C2:C5)

Jika A2 berisi 12/1/2023 dan B2 berisi 12/31/2023, hasilnya adalah jumlah hari kerja di bulan Desember, tidak termasuk hari libur yang tercantum pada rentang C2:C5.

Mengatur hari kerja dan akhir pekan dengan WORKDAY.INTL()

WORKDAY.INTL() adalah versi lanjutan dari WORKDAY() yang memungkinkan Anda menyesuaikan hari apa yang dianggap akhir pekan.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Sebagai contoh:

=WORKDAY.INTL(A2, 10, "0110000")

Di sini, 0110000 menentukan bahwa Selasa (0) dan Rabu (1) adalah akhir pekan. Rumus menghitung tanggal 10 hari kerja setelah A2, tidak termasuk Selasa dan Rabu.

Kesimpulan

Mengelola waktu di Excel bisa terasa rumit, tetapi dengan fungsi yang tepat, hal ini jauh lebih mudah ditangani.

Jika Anda ingin memperkuat keterampilan Excel lebih lanjut, lihat Introduction to Excel kami untuk keterampilan dasar atau kursus Data Analysis in Excel untuk berlatih dengan data dunia nyata.

Untuk aplikasi yang lebih mahir, jalur keterampilan Excel Fundamentals menawarkan proyek dan latihan terpandu untuk membantu Anda menguasai perhitungan waktu dan seterusnya.