Cara Rekap Absen & Gaji Karyawan Otomatis di Excel / Google Sheets
Akhir bulan biasanya jadi hari paling ribet buat pemilik toko kecil: buka buku absen, hitung siapa yang telat, siapa yang lembur, siapa yang alpha, lalu hitung gaji satu per satu pakai kalkulator. Panduan ini menjelaskan cara membuat rekap absen dan gaji karyawan yang menghitung sendiri di Excel atau Google Sheets, lengkap dengan rumusnya.
Struktur file yang dibutuhkan
Pisahkan data ke beberapa sheet supaya rumusnya rapi dan tidak gampang rusak:
- Pengaturan: jam masuk, jam pulang, toleransi telat, potongan telat per menit, hari kerja sebulan, uang makan, tarif lembur, dan bulan yang mau direkap.
- Karyawan: ID, nama, jabatan, tipe gaji (bulanan/harian), gaji pokok atau upah harian, tunjangan.
- Absensi: satu baris per karyawan per hari.
- Rekap dan Gaji: semuanya rumus, tidak diketik manual.
; menjadi ,.Langkah 1: tabel absensi harian
Buat kolom berikut di sheet Absensi:
Tanggal | ID | Nama | Jam Masuk | Jam Pulang | Status | Telat (menit) | Lembur (jam)
- Status sebaiknya pakai dropdown (Data → Validasi data): Hadir, Izin, Sakit, Cuti, Alpha. Penulisan yang seragam penting, karena rekap menghitung berdasarkan teks ini.
- Nama diisi otomatis dari ID supaya tidak salah ketik:
=IFERROR(VLOOKUP(B2;Karyawan!A:B;2;FALSE);"ID tidak ada") - Isi jam dengan format 24 jam, misalnya
08:00dan17:30.
Langkah 2: hitung telat & lembur otomatis
Di Excel dan Sheets, jam disimpan sebagai pecahan hari. Jadi selisih jam dikali 1440 untuk mendapat menit, atau dikali 24 untuk mendapat jam. Misalkan jam masuk normal ada di sel bernama JAM_MASUK dan toleransi di TOLERANSI:
Telat (menit), hanya dihitung kalau lewat toleransi:
=IF(F2="Hadir";IF(ROUND((D2-JAM_MASUK)*1440)>TOLERANSI;ROUND((D2-JAM_MASUK)*1440);0);"")
Lembur (jam), dibulatkan per 30 menit:
=IF(F2="Hadir";IF(E2>JAM_PULANG;ROUND((E2-JAM_PULANG)*48)/2;0);"")
Contoh: masuk 08:25 dengan toleransi 10 menit menghasilkan telat 25 menit. Pulang 19:00 dengan jam pulang normal 17:00 menghasilkan lembur 2 jam.
JAM_MASUK. Rumus jadi lebih mudah dibaca, dan kalau jam kerja berubah, cukup ubah satu sel.Langkah 3: rekap absen bulanan per karyawan
Kuncinya fungsi COUNTIFS (menghitung baris yang memenuhi beberapa syarat) dan SUMIFS (menjumlahkan). Simpan tanggal 1 bulan laporan di sel bernama BULAN, lalu di sheet Rekap:
Jumlah hadir karyawan di baris 2 (ganti "Hadir" dengan Izin/Sakit/Cuti/Alpha untuk kolom lain):
=COUNTIFS(Absensi!B:B;A2;Absensi!F:F;"Hadir";Absensi!A:A;">="&BULAN;Absensi!A:A;"<"&EDATE(BULAN;1))
Total telat sebulan:
=SUMIFS(Absensi!G:G;Absensi!B:B;A2;Absensi!A:A;">="&BULAN;Absensi!A:A;"<"&EDATE(BULAN;1))
Dengan syarat tanggal ini, data bulan lalu tetap tersimpan di tab Absensi. Untuk ganti bulan, cukup ubah isi sel BULAN.
Langkah 4: hitung gaji (bulanan & harian)
Untuk karyawan bulanan, gaji pokok dibayar penuh lalu dipotong per hari alpha. Untuk karyawan harian, upah dikali jumlah hari hadir:
=IF(C2="Harian";D2*E2;MAX(0;D2-D2/HARI_KERJA*F2))
C = tipe gaji, D = gaji pokok/upah harian, E = jumlah hadir, F = jumlah alpha.
Lalu total gaji:
= Gaji pokok dibayar + Tunjangan + (Hadir × Uang makan) + (Jam lembur × Tarif lembur) − (Menit telat × Potongan per menit)
| Contoh | Rina (bulanan) | Budi (harian) |
|---|---|---|
| Gaji pokok / upah | Rp3.000.000 | Rp110.000 × 5 hadir = Rp550.000 |
| Tunjangan | Rp300.000 | - |
| Uang makan (5 hadir × Rp15.000) | Rp75.000 | Rp75.000 |
| Lembur (× Rp15.000/jam) | 2 jam = Rp30.000 | 4,5 jam = Rp67.500 |
| Potongan telat (× Rp500/menit) | 25 menit = −Rp12.500 | 40 menit = −Rp20.000 |
| Total | Rp3.392.500 | Rp672.500 |
Langkah 5: slip gaji
Buat sheet Slip berisi satu dropdown nama karyawan. Cari baris karyawan itu dengan MATCH, lalu ambil angkanya dengan INDEX:
=INDEX(Gaji!L2:L51;MATCH(B2;Gaji!B2:B51;0))
Setiap ganti nama di dropdown, slip berubah. Tinggal screenshot dan kirim ke WhatsApp karyawan.
Catatan soal aturan lembur
Contoh di atas memakai tarif lembur rata per jam, yang umum dipakai usaha kecil. Untuk pekerja yang tunduk pada aturan ketenagakerjaan, PP Nomor 35 Tahun 2021 mengatur upah lembur 1,5× upah sejam untuk jam pertama dan 2× untuk jam berikutnya pada hari kerja biasa, dengan upah sejam = 1/173 × upah sebulan. Sesuaikan tarif di pengaturan dengan aturan yang berlaku untuk usaha Anda. Artikel ini panduan teknis, bukan nasihat hukum.
Mau langsung pakai tanpa merakit?
Template Rekap Absen & Gaji Karyawan Otomatis (Google Sheets): semua rumus di atas sudah terpasang dan dites, ditambah dropdown, warna otomatis untuk alpha dan telat, rekap bulanan, gaji bulanan & harian, dan slip gaji siap kirim. Contoh data disertakan.