Absensi manual yang direkap ulang di akhir bulan sering menyita waktu HR berjam-jam, apalagi kalau harus menghitung sisa cuti dan jam lembur satu per satu untuk puluhan karyawan. Dengan struktur sheet dan rumus yang tepat, Excel bisa menghitung semuanya otomatis begitu data harian diinput.
Cara membuat absensi karyawan otomatis di Excel sebenarnya bertumpu pada tiga elemen yang saling terhubung: input data harian yang konsisten, rumus rekap yang menarik data dari input tersebut, dan aturan bisnis yang jelas untuk menghitung sisa cuti serta lembur. Artikel ini membahas langkah demi langkah membangun sistem absensi ini dari nol, termasuk rumus untuk memantau sisa cuti tahunan dan menghitung kompensasi lembur secara otomatis sepanjang tahun.
5Sheet yang dibutuhkan
12Hari cuti tahunan (UU)
6Status kehadiran
2026Update Excel 365
Struktur Sheet yang Dibutuhkan
Master Karyawan
Data dasar: nama, jabatan, tanggal mulai kerja, dan jatah cuti tahunan sebagai acuan seluruh perhitungan sisa cuti.
Input Absensi Harian
Sheet tempat status kehadiran diinput setiap hari lewat dropdown, satu baris per karyawan per hari atau format matriks tanggal x nama.
Rekap Bulanan
Rumus COUNTIF/COUNTIFS yang menjumlahkan total Hadir, Izin, Sakit, Alpha, dan Cuti per karyawan setiap bulan.
Sisa Cuti
Menghitung jatah cuti dikurangi cuti yang sudah terpakai sepanjang tahun berjalan, update otomatis setiap kali cuti baru diinput.
Perhitungan Lembur
Menghitung selisih jam pulang aktual dengan jam kerja normal, dikonversi menjadi durasi lembur dan nilai kompensasinya.
Langkah 1: Setup Sheet Master Karyawan
Buat kolom: Nama, Jabatan, Tanggal Mulai Kerja, dan Jatah Cuti Tahunan. Berdasarkan Pasal 79 ayat (3) UU Ketenagakerjaan No. 13/2003 jo. UU Cipta Kerja No. 11/2020, karyawan berhak atas cuti tahunan paling sedikit 12 hari kerja setelah bekerja 12 bulan secara terus-menerus — angka ini bisa dijadikan default di kolom Jatah Cuti Tahunan, lalu disesuaikan jika kebijakan perusahaan memberikan lebih dari itu.
Langkah 2: Input Absensi Harian dengan Data Validation
Di sheet Input Absensi Harian, buat kolom tanggal di baris pertama dan nama karyawan di kolom pertama. Blok area input, buka Data > Data Validation > List, lalu masukkan daftar status: H (Hadir), I (Izin), S (Sakit), A (Alpha), C (Cuti), L (Lembur/Dinas Luar). Dropdown ini mencegah kesalahan ketik yang bisa membuat rumus rekap gagal mengenali status tertentu.
Gunakan Conditional Formatting pada kolom Sisa Cuti untuk menyorot otomatis karyawan yang sisa cutinya tinggal 2 hari atau kurang, sehingga HR bisa mengingatkan karyawan untuk memanfaatkan cutinya sebelum tahun berakhir.
Langkah 5: Rumus Hitung Lembur Otomatis
Untuk menghitung lembur, tambahkan kolom Jam Pulang Normal dan Jam Pulang Aktual di sheet Input Absensi Harian, format sebagai Time (jam:menit).
Kalikan hasil selisih jam dengan angka 24 karena Excel menyimpan waktu sebagai pecahan hari (1 = 24 jam), sehingga selisih dua sel Time perlu dikonversi ke satuan jam agar bisa dikalikan dengan tarif lembur per jam secara langsung.
“Sistem absensi yang baik bukan yang paling rumit rumusnya, melainkan yang paling konsisten formatnya — satu kesalahan format tanggal atau status bisa membuat seluruh rekap bulanan meleset tanpa terlihat.” — Prinsip dasar desain sistem administrasi kepegawaian.
Studi Kasus: Rekap Bulanan Karyawan
Misalnya seorang karyawan dengan jatah cuti 12 hari sudah mengambil 5 hari cuti hingga bulan Agustus, sehingga sisa cutinya otomatis terhitung 7 hari lewat rumus INDEX/MATCH tanpa perlu dihitung manual. Di bulan yang sama, karyawan tersebut lembur 3 kali dengan total 6 jam — rumus SUMIFS otomatis menjumlahkan durasi tersebut dan mengalikannya dengan tarif lembur yang sudah ditetapkan di Master Karyawan, menghasilkan nilai kompensasi lembur bulanan tanpa perhitungan manual satu per satu.
Kesalahan Umum Membuat Absensi Otomatis
Perhatikan hal berikut agar sistem absensi tetap akurat:
Membiarkan kolom status diisi bebas tanpa Data Validation, sehingga variasi ejaan seperti “hadir” dan “Hadir” dihitung sebagai dua status berbeda oleh rumus COUNTIF yang case-sensitive terhadap teks. Lupa mengunci format kolom Jam Pulang sebagai Time, membuat rumus selisih jam menghasilkan angka yang salah. Tidak memisahkan sheet input dari sheet rekap, sehingga rumus rekap ikut berubah saat seseorang tidak sengaja mengedit data input mentah.
Tips Tambahan untuk Sistem Absensi yang Lebih Kuat
Ubah range data di setiap sheet menjadi Excel Table (Ctrl+T) agar rumus tetap valid ketika baris karyawan baru ditambahkan di tengah tahun. Untuk perusahaan dengan banyak karyawan, pertimbangkan menggunakan PivotTable di sheet Rekap Bulanan agar ringkasan per divisi atau per bulan bisa difilter tanpa mengubah rumus dasar yang sudah dibangun.
Pertanyaan Seputar Absensi Karyawan Otomatis di Excel
Berapa hari cuti tahunan minimal yang wajib diberikan perusahaan?
Berdasarkan Pasal 79 ayat (3) UU Ketenagakerjaan jo. UU Cipta Kerja, minimal 12 hari kerja setelah karyawan bekerja 12 bulan secara terus-menerus. Perusahaan boleh memberikan lebih dari itu sesuai kebijakan internal.
Kenapa rumus COUNTIF tidak menghitung status yang sudah diinput?
Kemungkinan besar ada spasi tambahan atau perbedaan huruf kapital pada teks status. Pastikan seluruh input menggunakan Data Validation dropdown agar teks yang tersimpan benar-benar konsisten.
Bagaimana cara menghitung lembur yang melewati tengah malam?
Tambahkan logika IF tambahan: jika Jam Pulang Aktual lebih kecil dari Jam Pulang Normal (menandakan lewat tengah malam), tambahkan 24 jam ke hasil selisih sebelum dikalikan dengan tarif lembur.
Apakah template ini bisa dipakai untuk menghitung upah lembur sesuai aturan pemerintah?
Rumus dasar bisa disesuaikan dengan formula pengali lembur resmi (misalnya 1,5x upah per jam untuk jam pertama), namun sebaiknya berkonsultasi dengan HR atau referensi resmi Kemnaker untuk memastikan perhitungan sesuai regulasi pengupahan yang berlaku.
Bagaimana cara mencegah sisa cuti minus?
Tambahkan rumus validasi tambahan di sheet Sisa Cuti menggunakan Conditional Formatting atau Data Validation custom yang menolak input cuti baru jika sisa cuti karyawan tersebut sudah menunjukkan angka nol.
Kesimpulan
Membuat absensi karyawan otomatis di Excel yang mencakup sisa cuti dan lembur sebenarnya tidak memerlukan rumus yang sangat rumit — kuncinya ada pada struktur sheet yang jelas dan konsistensi format input. Data Validation memastikan setiap status kehadiran seragam, COUNTIF/COUNTIFS merekap data secara otomatis, sementara INDEX/MATCH dan SUMIFS menghubungkan data cuti serta lembur ke masing-masing karyawan tanpa perhitungan manual berulang. Setelah sistem ini berjalan, HR bisa memantau sisa cuti dan total lembur kapan saja secara real-time, cukup dengan menginput data harian secara rutin dan konsisten sepanjang tahun.
Lengkapi sistem absensi ini dengan template gaji karyawan yang bisa menarik data jam kerja dan lembur secara otomatis.
