Proses payroll 200+ karyawan dari 5 hari jadi 2 hari
Proses payroll 200+ karyawan memakan 5 hari kerja setiap bulan karena data absensi dari mesin fingerprint harus dihitung lembur manual per karyawan. Power Query mengotomatisasi transformasi data absensi sehingga total proses turun 60%.
Masalah
Data absensi dari mesin fingerprint harus diexport manual lalu dihitung lembur per karyawan satu per satu sesuai aturan shift yang berbeda-beda. Proses payroll memakan waktu 5 hari kerja setiap bulan dengan risiko kesalahan hitung yang tinggi.
Rincian Tantangan
- 1Export mesin fingerprint menghasilkan data mentah — satu baris per tap, bukan per hari kerja
- 2Ada 3 jenis shift berbeda (pagi, siang, malam) dengan aturan lembur yang berbeda per shift
- 3Karyawan yang lupa tap OUT harus dicek manual ke pengawas, memperlambat proses
- 4Tidak ada validasi otomatis — kesalahan hitung lembur baru ketahuan saat karyawan komplain
- 5Proses rekap absensi saja (belum payroll) membutuhkan 2 hari kerja penuh
Solusi
Power Query untuk import dan transformasi otomatis data absensi dari export mesin fingerprint. Formula Excel untuk kalkulasi lembur sesuai aturan shift per departemen. Pivot Table untuk summary rekapitulasi per departemen yang langsung dipakai approval.
Langkah-Langkah Implementasi
- 1
Export data absensi dari software mesin fingerprint ke format .CSV
- 2
Power Query → From Text/CSV → pilih file export
- 3
Filter hanya kolom: NIK, Nama, Tanggal, Waktu, Jenis (IN/OUT)
- 4
Group By NIK + Tanggal → MIN(Waktu) untuk jam masuk, MAX(Waktu) untuk jam keluar
- 5
Merge dengan tabel Master Karyawan (NIK, Shift, Gaji Pokok, Departemen)
- 6
Add Column: Durasi = [Jam Keluar] - [Jam Masuk] (dalam jam desimal)
- 7
Add Column: Jam Lembur = Number.Max(0, [Durasi] - 8)
- 8
Add Column: Upah Lembur = [Gaji Pokok] / 173 * 1.5 * [Jam Lembur]
- 9
Close & Load ke sheet 'Rekap Absensi'
- 10
Pivot Table: Rows = Departemen → Nama, Values = Total Lembur (jam) + Total Upah
- 11
Conditional formatting: highlight karyawan lembur > 56 jam/bulan (batas UU)
Sebelum vs Sesudah
Export mesin fingerprint — format raw, satu baris per tap
| NIK | Nama | Tanggal | Waktu | Jenis | Device |
|---|---|---|---|---|---|
| EMP-0023 | Siti Rahayu | 2025-06-01 | 07:58:22 | IN | FP-Pintu A |
| EMP-0023 | Siti Rahayu | 2025-06-01 | 17:02:11 | OUT | FP-Pintu A |
| EMP-0047 | Ahmad Fauzi | 2025-06-01 | 13:01:45 | IN | FP-Pintu B |
Tabel kalkulasi lembur per karyawan per hari
| NIK | Nama | Shift | Jam Kerja | Lembur (jam) | Upah Lembur |
|---|---|---|---|---|---|
| EMP-0023 | Siti Rahayu | Pagi (07-15) | 9.07 | 1.07 | Rp 28.750 |
| EMP-0047 | Ahmad Fauzi | Siang (13-21) | 8.55 | 0.55 | Rp 14.375 |
| EMP-0061 | Dewi Kartika | Malam (21-05) | 8.20 | 0.20 | Rp 5.375 |
Hasil
5 hari → 2 hari
Rekap absensi turun dari 2 hari ke 2 jam, total proses payroll 60% lebih cepat