TL;DR
Cara menghitung selisih jam di Excel adalah dengan mengurangkan jam keluar dari jam masuk (=B2-A2), lalu memformat hasilnya sebagai [h]:mm supaya total di atas 24 jam nggak balik ke nol. Untuk shift malam yang lewat tengah malam, pakai =MOD(B2-A2,1) biar hasilnya nggak minus. Kalau mau angka jam desimal untuk hitung lembur, kalikan hasilnya dengan 24.
Menghitung jam di Excel itu pengurangan biasa: jam keluar dikurangi jam masuk. =B2-A2, selesai.
Masalahnya, Excel nyimpen waktu dengan cara yang aneh. Jadi hasilnya sering muncul #####, atau total 26 jam malah tampil jadi 2 jam.
Di sini aku bahas rumus dasarnya, cara nanganin shift malam, dan gimana ngubah jam jadi rupiah lembur, pakai data absensi toko_berkah sebagai contoh.
Kurangkan sel jam keluar dengan sel jam masuk, lalu format hasilnya sebagai [h]:mm. Kalau jam masuk ada di A2 dan jam keluar di B2, rumusnya =B2-A2. Hasilnya durasi kerja dalam jam dan menit. Format kurung siku bikin total di atas 24 jam tetap kebaca bener.
A2 = 08:00 (jam masuk)
B2 = 17:30 (jam keluar)
C2 = =B2-A2 → 09:30Kenapa bisa langsung dikurangi? Soalnya di balik layar, Excel nyimpen waktu sebagai pecahan dari 1 hari.
Jam 12:00 siang itu 0,5. Jam 06:00 pagi itu 0,25. Jam 18:00 itu 0,75.
Jadi waktu kamu ngurangin dua jam, kamu sebenernya lagi ngurangin dua angka desimal. Excel cuma nampilin hasilnya balik dalam bentuk jam soalnya format selnya diatur begitu.
Ada tiga penyebab yang paling sering aku temuin.
Ini yang paling gampang. Excel nggak muat nampilin isinya, jadi dia kasih pagar. Double-click di batas kolom, lebarin, beres.
Excel nggak bisa nampilin waktu negatif pakai format waktu standar. Kalau jam keluar lebih kecil dari jam masuk (misal shift malam masuk 22:00 pulang 06:00), hasilnya minus dan langsung jadi #####.
Solusinya di section shift malam di bawah.
Kalau hasilnya keluar angka kayak 0,395833, berarti sel hasilnya format General atau Number. Excel-nya bener kok, tampilannya doang yang belum diatur.
Blok selnya, tekan Ctrl + 1, pilih Custom, ketik [h]:mm.
Kurung siku bikin Excel berhenti nge-reset jam tiap lewat 24. Tanpa kurung siku, total 26 jam bakal ditampilin sebagai 02:00. Soalnya Excel anggap itu "jam 2 di hari berikutnya", bukan durasi 26 jam.
| Format | Total sebenernya | Yang tampil | Kapan dipakai |
|---|---|---|---|
h:mm | 26 jam | 2:00 ❌ | Nampilin jam biasa (08:00) |
[h]:mm | 26 jam | 26:00 ✅ | Total durasi kerja |
[m] | 26 jam | 1560 | Total dalam menit |
Aturan gampangnya: kolom durasi selalu pakai kurung siku. Kolom jam masuk/keluar nggak usah.
Ini bug diam-diam yang paling sering bikin rekap absensi salah. Angkanya kelihatan wajar, jadi nggak ada yang curiga sampai gaji cair.
Bungkus pengurangannya pakai MOD. Rumusnya =MOD(B2-A2,1). Kalau hasilnya positif, MOD nggak ngapa-ngapain. Kalau negatif (kayak shift masuk 22:00 pulang 06:00), MOD nambahin 1 hari penuh, jadi hasilnya balik positif dan bener.
A2 = 22:00 (masuk shift malam)
B2 = 06:00 (pulang besok pagi)
=B2-A2 → ##### (minus, error)
=MOD(B2-A2,1) → 08:00 ✅Cara kerjanya: 06:00 - 22:00 menghasilkan -0,666. MOD bagi angka itu sama 1 dan ambil sisanya, yang hasilnya 0,333, alias 8 jam.
Aku selalu pakai MOD sebagai default di kolom durasi, bahkan buat shift pagi. Hasilnya sama persis buat shift normal, tapi kamu aman kalau tiba-tiba ada karyawan yang kerja lintas hari.
Detail rumus MOD ada di halaman fungsi MOD, sekalian contoh pemakaian di luar konteks jam.
Kalikan 24. Selesai.
Excel nyimpen waktu sebagai pecahan hari, dan 1 hari = 24 jam. Jadi =(B2-A2)*24 ngasih kamu angka jam desimal yang siap dikali tarif.
=(B2-A2)*24 → 9,5 (9 jam 30 menit)
=MOD(B2-A2,1)*24 → 8 (versi aman shift malam)Penting: sel hasilnya harus format Number, bukan Time. Kalau masih format Time, Excel bakal nampilin 9,5 sebagai jam lagi dan kamu bingung sendiri.
Buat jam lembur, kurangi jam normal dan kunci pakai MAX:
=MAX(0, MOD(B2-A2,1)*24 - 8)MAX(0, ...) bikin hasilnya nggak pernah minus. Karyawan yang pulang jam 4 sore nggak bakal kecatat punya lembur -1 jam. Detail fungsinya ada di halaman fungsi MAX.
Ini data absensi 5 karyawan toko_berkah selama satu minggu di bulan Maret. Tarif lembur Rp 25.000 per jam, jam kerja normal 8 jam.
| Nama | Masuk | Keluar | Total jam | Lembur (jam) | Upah lembur |
|---|---|---|---|---|---|
| Siti | 08:00 | 17:00 | 9:00 | 1,0 | Rp 25.000 |
| Budi | 08:15 | 19:30 | 11:15 | 3,25 | Rp 81.250 |
| Rina | 22:00 | 06:00 | 8:00 | 0,0 | Rp 0 |
| Agus | 09:00 | 16:00 | 7:00 | 0,0 | Rp 0 |
| Dewi | 21:30 | 07:15 | 9:45 | 1,75 | Rp 43.750 |
Rumus lengkap di kolom upah lembur:
Total jam : =MOD(C2-B2,1) format [h]:mm
Lembur : =MAX(0, MOD(C2-B2,1)*24-8) format Number
Upah : =E2*25000 format RupiahYang menarik: baris Rina dan Dewi bakal error ##### kalau rumusnya cuma =C2-B2. Dari 5 baris, 2 di antaranya rusak. 40% data absensi toko_berkah pakai shift malam.
Toko retail, warung 24 jam, minimarket. Shift malam itu normal di Indonesia, dan template absensi yang cuma pakai pengurangan biasa bakal jebol di sana.
08.00 pakai titik, Excel baca itu sebagai teks, bukan waktu. Pengurangannya bakal error #VALUE!. Selalu pakai titik dua: 08:00.MOD bakal ngitung itu sebagai 00:00. Tambahin IF: =IF(OR(B2="",C2=""),"",MOD(C2-B2,1)).=MOD(C2-B2,1)-TIME(1,0,0).Buat pemahaman lebih dalam soal gimana Excel nyimpen tanggal dan waktu sebagai angka, dokumentasi resmi Microsoft soal format tanggal dan waktu ngejelasin sistem serial number-nya.
Ada dua penyebab. Pertama, kolomnya kesempitan, tinggal lebarin kolomnya, beres. Kedua, hasil pengurangannya minus, dan Excel nggak bisa nampilin waktu negatif di format waktu standar. Ini biasanya kejadian di shift malam, jam keluar 06:00 dikurangi jam masuk 22:00 jadi negatif. Ganti rumusnya jadi =MOD(B2-A2,1) dan tanda pagarnya hilang.
Kalikan hasilnya dengan 24. Excel nyimpen waktu sebagai pecahan hari, jadi 08:30 itu sebenernya 0,354166. Rumus =(B2-A2)*24 ngasih 8,5, angka yang siap dikali tarif per jam. Jangan lupa ganti format selnya jadi Number, bukan Time, kalau nggak hasilnya bakal ditampilin sebagai jam lagi.
Format h:mm balik ke nol tiap lewat 24 jam. Jadi total 26 jam kerja bakal ditampilin sebagai 2:00. Salah, dan gampang kelewat. Kurung siku di [h]:mm bilang ke Excel biar jamnya terus dihitung tanpa reset, jadi 26 jam tetap tampil 26:00. Buat kolom total jam kerja bulanan, selalu pakai [h]:mm.
Pakai MAX supaya jam lembur nggak pernah minus. Rumusnya =MAX(0,(B2-A2)*24-8): total jam kerja dikurangi 8 jam normal, dan kalau hasilnya di bawah nol, MAX bikin jadi 0. Habis itu tinggal dikali tarif lembur per jam untuk dapat rupiahnya. Kalau nggak pakai MAX, karyawan yang pulang cepat malah kelihatan punya lembur negatif.
Tiga hal yang perlu kamu inget:
=B2-A2, tapi pakai =MOD(B2-A2,1) sebagai default biar shift malam aman.[h]:mm, kalau nggak total di atas 24 jam bakal ke-reset diam-diam.Coba praktekin langsung di file absensi kamu sendiri. Ganti rumus pengurangan biasa jadi MOD, terus cek kolom totalnya. Kalau angkanya berubah, berarti selama ini ada jam yang hilang.
Lanjut belajar: rumus DATEDIF buat hitung selisih tanggal dan SUMIF buat nge-total jam per karyawan.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.