Contoh Rumus Excel IF untuk Absensi Karyawan dan Rekap Kehadiran HRD

Rumus Excel IF untuk absensi karyawan sangat membantu HRD dalam mengelola data kehadiran, keterlambatan, izin, sakit, cuti, lembur, dan jam kerja. Dengan rumus yang tepat, perusahaan tidak perlu lagi menghitung status kehadiran satu per satu secara manual.

Jika jumlah karyawan masih sedikit, absensi manual mungkin masih bisa dikerjakan. Namun, ketika jumlah karyawan bertambah, pencatatan manual akan semakin menyita waktu dan rentan salah. HR harus memeriksa jam masuk, jam pulang, shift, status izin, lembur, dan total hari kerja dalam satu periode. Kesalahan kecil dalam absensi juga dapat memengaruhi gaji, tunjangan, upah lembur, sampai penilaian kedisiplinan karyawan.

Di sinilah Excel masih sering menjadi alat bantu yang praktis. Dengan fungsi IF, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS, dan beberapa rumus waktu, HR dapat membuat template absensi yang lebih otomatis. Rumus ini bisa membantu menandai apakah karyawan hadir tepat waktu, terlambat, tidak hadir, bekerja lembur, atau memenuhi jumlah jam kerja yang ditentukan.

Artikel ini akan membahas pengertian rumus IF, contoh rumus Excel IF untuk absensi karyawan, rumus keterlambatan, rumus jam kerja, rumus shift, rumus lembur, rumus rekap kehadiran bulanan, hingga tips agar file absensi Excel lebih aman dan mudah digunakan.

Apa Itu Rumus Excel IF?

Rumus IF adalah fungsi Excel yang digunakan untuk membuat pengujian logika. Secara sederhana, IF berarti “jika”. Rumus ini akan memeriksa sebuah kondisi, lalu menampilkan hasil tertentu jika kondisi tersebut benar dan hasil lain jika kondisi tersebut salah.

Format dasar rumus IF adalah:

=IF(logical_test,value_if_true,value_if_false)

Dalam pengaturan regional tertentu, pemisah argumen Excel dapat menggunakan titik koma. Jadi rumusnya bisa ditulis seperti ini:

=IF(logical_test;value_if_true;value_if_false)

Contoh sederhana:

=IF(B2>08:00;"Terlambat";"Tepat Waktu")

Artinya, jika jam masuk di sel B2 lebih besar dari jam 08.00, maka Excel akan menampilkan “Terlambat”. Jika tidak, Excel akan menampilkan “Tepat Waktu”.

Dalam konteks HR, fungsi IF dapat digunakan untuk menilai status absensi berdasarkan kondisi tertentu. Misalnya, jika jam masuk kosong maka status “Tidak Absen”, jika jam masuk melewati batas toleransi maka status “Terlambat”, dan jika jam masuk sesuai aturan maka status “Hadir”.

Untuk memahami konteks absensi secara lebih lengkap, Anda juga dapat membaca artikel ketentuan cara menghitung absensi karyawan yang benar dan contoh form absensi karyawan format Excel XLS.

Mengapa Rumus IF Berguna untuk Absensi Karyawan?

Rumus IF berguna karena absensi karyawan biasanya membutuhkan banyak keputusan logika. HR tidak hanya mencatat hadir atau tidak hadir, tetapi juga perlu membaca kondisi lain seperti jam masuk, jam pulang, shift kerja, izin, sakit, cuti, lembur, dan total jam kerja.

Contoh logika absensi yang sering muncul:

• Jika karyawan masuk sebelum jam 08.00, maka statusnya “Tepat Waktu”.
• Jika karyawan masuk setelah jam 08.15, maka statusnya “Terlambat”.
• Jika karyawan tidak memiliki jam masuk, maka statusnya “Tidak Absen Masuk”.
• Jika karyawan tidak hadir tetapi ada keterangan “Sakit”, maka statusnya “Sakit”.
• Jika karyawan tidak hadir tanpa keterangan, maka statusnya “Alpa”.
• Jika jam pulang lebih dari jadwal kerja, maka ada kemungkinan lembur.
• Jika total jam kerja kurang dari standar, maka perlu pengecekan.

Jika semua kondisi tersebut dihitung manual, HR akan menghabiskan banyak waktu. Dengan Excel, sebagian besar status dapat dibuat otomatis selama struktur datanya rapi.

Namun, rumus Excel tetap harus digunakan dengan hati-hati. Jika rumus salah, hasil absensi juga salah. Karena data absensi sering terhubung dengan payroll, kesalahan tersebut dapat berdampak pada siklus penggajian, perhitungan gaji bersih di Excel, dan pembuatan slip gaji karyawan.

Struktur Data Absensi yang Disarankan di Excel

Sebelum membuat rumus IF, HR perlu menyusun struktur data absensi dengan rapi. Struktur tabel yang tidak konsisten akan membuat rumus sulit digunakan dan rawan error.

Berikut contoh kolom absensi harian yang bisa digunakan:

Struktur ini bisa disesuaikan dengan kebutuhan perusahaan. Jika perusahaan memiliki banyak shift, tambahkan sheet khusus untuk data master shift. Jika perusahaan masih memakai form absensi manual, data dapat dikonversi terlebih dahulu ke format Excel.

Untuk perusahaan yang sudah mulai mempertimbangkan digitalisasi, baca juga artikel keuntungan menggunakan software absensi online bagi perusahaan dan karyawan serta efektivitas penerapan absensi online karyawan.

Contoh Rumus Excel IF untuk Status Kehadiran

Rumus paling dasar dalam absensi adalah menentukan status hadir atau tidak hadir. Misalnya, jika kolom Jam Masuk Aktual kosong, maka statusnya “Tidak Hadir”. Jika ada jam masuk, maka statusnya “Hadir”.

Rumus dengan Pemisah Koma

=IF(H2="","Tidak Hadir","Hadir")

Rumus dengan Pemisah Titik Koma

=IF(H2="";"Tidak Hadir";"Hadir")

Penjelasan:

• H2 adalah sel Jam Masuk Aktual.
• Jika H2 kosong, Excel menampilkan “Tidak Hadir”.
• Jika H2 berisi jam masuk, Excel menampilkan “Hadir”.

Rumus ini cocok untuk absensi sederhana. Namun, dalam praktik HR, tidak hadir bisa memiliki beberapa keterangan, seperti sakit, izin, cuti, alpa, atau dinas luar. Karena itu, rumus perlu dikembangkan dengan kondisi tambahan.

Contoh Rumus IF untuk Status Hadir, Sakit, Izin, Cuti, dan Alpa

Misalnya kolom H berisi Jam Masuk Aktual dan kolom J berisi Keterangan. Jika ada jam masuk, statusnya “Hadir”. Jika tidak ada jam masuk tetapi keterangan berisi “Sakit”, maka statusnya “Sakit”. Jika keterangan berisi “Izin”, maka statusnya “Izin”. Jika tidak ada keterangan, maka statusnya “Alpa”.

Rumus dengan Nested IF

=IF(H2<>"";"Hadir";IF(J2="Sakit";"Sakit";IF(J2="Izin";"Izin";IF(J2="Cuti";"Cuti";"Alpa"))))

Penjelasan:

• Jika H2 tidak kosong, karyawan dianggap hadir.
• Jika H2 kosong dan J2 berisi “Sakit”, statusnya Sakit.
• Jika H2 kosong dan J2 berisi “Izin”, statusnya Izin.
• Jika H2 kosong dan J2 berisi “Cuti”, statusnya Cuti.
• Jika tidak memenuhi semua kondisi tersebut, statusnya Alpa.

Nested IF berguna untuk beberapa kondisi, tetapi jika kondisinya terlalu banyak, rumus bisa menjadi panjang dan sulit diaudit. Untuk data yang lebih kompleks, HR bisa menggunakan kolom referensi, Data Validation, atau fungsi lain seperti IFS jika tersedia di versi Excel yang digunakan.

Contoh Rumus Excel IF untuk Menentukan Karyawan Terlambat

Rumus IF juga dapat digunakan untuk menentukan apakah karyawan terlambat atau tepat waktu. Misalnya jadwal masuk ada di F2 dan jam masuk aktual ada di H2.

Rumus Status Terlambat Sederhana

=IF(H2="";"Tidak Absen";IF(H2>F2;"Terlambat";"Tepat Waktu"))

Penjelasan:

• Jika H2 kosong, statusnya “Tidak Absen”.
• Jika H2 lebih besar dari F2, statusnya “Terlambat”.
• Jika tidak, statusnya “Tepat Waktu”.

Contoh:

Contoh Rumus IF untuk Toleransi Keterlambatan

Banyak perusahaan memiliki toleransi keterlambatan. Misalnya, jadwal masuk 08.00 tetapi karyawan masih dianggap tepat waktu sampai 08.15. Dalam kasus ini, rumus perlu menambahkan batas toleransi.

Misalnya:

• F2 = Jadwal Masuk.
• H2 = Jam Masuk Aktual.
• B1 = Batas Toleransi dalam format waktu, misalnya 00:15.

Rumus dengan Toleransi

=IF(H2="";"Tidak Absen";IF(H2>F2+$B$1;"Terlambat";"Tepat Waktu"))

Penjelasan:

• Jika jam masuk kosong, statusnya “Tidak Absen”.
• Jika jam masuk lebih dari jadwal masuk ditambah toleransi, statusnya “Terlambat”.
• Jika masih dalam batas toleransi, statusnya “Tepat Waktu”.

Penggunaan referensi absolut seperti $B$1 berguna agar sel toleransi tidak berubah saat rumus disalin ke baris lain.

Contoh Rumus IF untuk Menghitung Durasi Terlambat

Selain status terlambat, HR sering perlu mengetahui berapa lama karyawan terlambat. Misalnya jadwal masuk di F2 dan jam masuk aktual di H2.

Rumus Durasi Terlambat Tanpa Toleransi

=IF(H2="";0;IF(H2>F2;H2-F2;0))

Jika hasilnya berupa angka desimal, ubah format sel menjadi Time atau Custom dengan format:

[h]:mm

Dengan format tersebut, durasi keterlambatan dapat tampil sebagai 00:10, 00:30, atau 01:15.

Rumus Durasi Terlambat dengan Toleransi

Jika toleransi keterlambatan berada di B1:

=IF(H2="";0;IF(H2>F2+$B$1;H2-(F2+$B$1);0))

Rumus ini hanya menghitung keterlambatan setelah melewati batas toleransi. Misalnya jadwal masuk 08.00, toleransi 15 menit, dan karyawan masuk 08.20, maka durasi terlambat yang dihitung adalah 5 menit.

Contoh Rumus IF untuk Jam Pulang Lebih Awal

Selain jam masuk, HR juga perlu memeriksa jam pulang. Karyawan yang pulang sebelum jadwal bisa diberi status “Pulang Cepat” atau “Kurang Jam”.

Misalnya:

• G2 = Jadwal Pulang.
• I2 = Jam Pulang Aktual.

Rumus Status Pulang Cepat

=IF(I2="";"Tidak Absen Pulang";IF(I2<G2;"Pulang Cepat";"Sesuai Jadwal"))

Rumus ini akan menandai karyawan yang tidak melakukan absensi pulang atau pulang sebelum jadwal kerja.

Contoh Rumus IF untuk Menghitung Jam Kerja Harian

Jam kerja harian dapat dihitung dari jam pulang aktual dikurangi jam masuk aktual, lalu dikurangi waktu istirahat jika diperlukan.

Misalnya:

• H2 = Jam Masuk Aktual.
• I2 = Jam Pulang Aktual.
• B2 = Durasi istirahat, misalnya 01:00.

Rumus Jam Kerja

=IF(OR(H2="";I2="");0;I2-H2-$B$2)

Jika Excel Anda memakai titik koma:

=IF(OR(H2="";I2="");0;I2-H2-$B$2)

Ubah format hasil menjadi:

[h]:mm

Rumus ini akan menghasilkan total jam kerja harian. Jika jam masuk atau jam pulang kosong, hasilnya 0 agar tidak muncul error.

Contoh Rumus IF untuk Shift Malam yang Lewat Tengah Malam

Rumus jam kerja biasa dapat bermasalah jika shift melewati tengah malam. Misalnya karyawan masuk pukul 22.00 dan pulang pukul 06.00. Jika langsung memakai rumus I2-H2, hasilnya bisa negatif.

Untuk mengatasi hal ini, gunakan rumus:

=IF(OR(H2="";I2="");0;IF(I2<H2;I2+1-H2;I2-H2))

Jika ingin mengurangi jam istirahat di B2:

=IF(OR(H2="";I2="");0;IF(I2<H2;I2+1-H2;I2-H2)-$B$2)

Penjelasan:

• Jika jam pulang lebih kecil dari jam masuk, Excel menganggap shift melewati hari berikutnya.
• Rumus menambahkan angka 1 untuk mewakili pergantian hari.
• Durasi istirahat dapat dikurangi jika perusahaan memberlakukannya.

Untuk pembahasan jadwal kerja, baca artikel pengaturan jam kerja dalam undang-undang, pembagian shift kerja yang HRD dan karyawan perlu tahu, dan rekomendasi aplikasi jadwal kerja shift terbaik.

Contoh Rumus IF untuk Menghitung Jam Lembur

Lembur dapat dihitung jika jam kerja aktual melebihi jam kerja normal atau jika jam pulang aktual melebihi jadwal pulang. Namun, perhitungan upah lembur perlu mengikuti ketentuan ketenagakerjaan dan kebijakan perusahaan.

Untuk menghitung durasi lembur sederhana, misalnya:

• G2 = Jadwal Pulang.
• I2 = Jam Pulang Aktual.

Rumus Durasi Lembur Sederhana

=IF(I2="";0;IF(I2>G2;I2-G2;0))

Jika shift malam melewati tengah malam, rumus perlu disesuaikan dengan logika hari berikutnya. Contoh:

=IF(OR(G2="";I2="");0;IF(I2<G2;I2+1-G2;IF(I2>G2;I2-G2;0)))

Rumus di atas hanya menghitung durasi lembur. Untuk menghitung upah lembur, perusahaan perlu mengikuti aturan yang berlaku dan data upah yang benar. Pembahasan lanjutan bisa dibaca di artikel rumus dan cara perhitungan lembur karyawan pada perusahaan serta contoh kekurangan menggunakan form lembur karyawan manual.

Contoh Rumus IF untuk Menentukan Status Lembur

Selain durasi, HR bisa membuat kolom status lembur agar mudah dibaca.

Misalnya durasi lembur berada di N2:

=IF(N2>0;"Lembur";"Tidak Lembur")

Jika perusahaan hanya menghitung lembur setelah minimal 30 menit, gunakan:

=IF(N2>=TIME(0;30;0);"Lembur";"Tidak Lembur")

Jika Excel Anda memakai koma:

=IF(N2>=TIME(0,30,0),"Lembur","Tidak Lembur")

Rumus ini berguna untuk menyaring karyawan yang lemburnya perlu diverifikasi oleh atasan atau HR.

Contoh Rumus IF untuk Menghitung Potongan Keterlambatan

Beberapa perusahaan memiliki kebijakan potongan keterlambatan. Kebijakan ini harus jelas, transparan, dan sesuai peraturan perusahaan atau perjanjian kerja. Jika perusahaan menerapkannya, Excel dapat digunakan untuk menghitung potongan berdasarkan durasi terlambat.

Misalnya:

• L2 = Durasi terlambat.
• B3 = Tarif potongan per menit, misalnya Rp1.000.

Karena waktu di Excel disimpan dalam satuan hari, durasi perlu dikonversi menjadi menit dengan mengalikan 1440.

Rumus Potongan Terlambat

=IF(L2>0;L2*1440*$B$3;0)

Contoh: jika durasi terlambat 00:10 dan potongan per menit Rp1.000, hasilnya Rp10.000.

Jika perusahaan tidak menerapkan potongan per menit, rumus bisa disesuaikan. Misalnya, terlambat lebih dari 30 menit dikenakan potongan tetap Rp25.000:

=IF(L2>TIME(0;30;0);25000;0)

Namun, sekali lagi, kebijakan potongan harus memiliki dasar yang jelas agar tidak menimbulkan konflik dengan karyawan.

Contoh Rumus IF untuk Validasi Data Absensi

Rumus IF tidak hanya digunakan untuk menghitung, tetapi juga untuk memeriksa data yang janggal. Validasi ini penting agar HR bisa menemukan kesalahan sebelum data dipakai untuk payroll.

1. Menandai Jam Pulang Kosong

=IF(AND(H2<>"";I2="");"Cek Absen Pulang";"OK")

Rumus ini akan menandai karyawan yang memiliki jam masuk tetapi tidak memiliki jam pulang.

2. Menandai Jam Masuk Kosong tetapi Jam Pulang Ada

=IF(AND(H2="";I2<>"");"Cek Absen Masuk";"OK")

3. Menandai Data Kosong Tanpa Keterangan

=IF(AND(H2="";I2="";J2="");"Butuh Keterangan";"OK")

4. Menandai Jam Kerja Terlalu Panjang

Misalnya jam kerja berada di M2. Jika lebih dari 12 jam, tandai untuk dicek:

=IF(M2>TIME(12;0;0);"Cek Jam Kerja";"OK")

Validasi seperti ini membantu HR menemukan data aneh lebih cepat. Jika perusahaan masih sering mengalami data hilang atau tidak lengkap, baca artikel penyebab dan solusi data absensi karyawan hilang serta cara memindahkan data absen fingerprint.

Contoh Rumus COUNTIF untuk Menghitung Jumlah Hadir

Setelah status harian dibuat, HR biasanya perlu membuat rekap bulanan. Fungsi COUNTIF dapat digunakan untuk menghitung jumlah data dengan satu kriteria.

Misalnya status kehadiran karyawan selama satu bulan berada di range K2:K31.

Menghitung Jumlah Hadir

=COUNTIF(K2:K31;"Hadir")

Menghitung Jumlah Terlambat

=COUNTIF(K2:K31;"Terlambat")

Menghitung Jumlah Alpa

=COUNTIF(K2:K31;"Alpa")

COUNTIF cocok untuk rekap sederhana. Namun, jika perlu menghitung berdasarkan nama karyawan, periode, dan status sekaligus, gunakan COUNTIFS.

Contoh Rumus COUNTIFS untuk Rekap Absensi per Karyawan

COUNTIFS digunakan untuk menghitung data dengan beberapa kriteria. Fungsi ini sangat berguna untuk rekap absensi bulanan berdasarkan ID karyawan dan status tertentu.

Misalnya:

• Kolom B = ID Karyawan.
• Kolom A = Tanggal.
• Kolom K = Status Kehadiran.
• Sel P2 = ID karyawan yang ingin direkap.
• Sel Q1 = tanggal awal periode.
• Sel R1 = tanggal akhir periode.

Menghitung Jumlah Hadir per Karyawan dalam Periode Tertentu

=COUNTIFS($B:$B;$P2;$K:$K;"Hadir";$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)

Menghitung Jumlah Terlambat per Karyawan

=COUNTIFS($B:$B;$P2;$K:$K;"Terlambat";$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)

Menghitung Jumlah Alpa per Karyawan

=COUNTIFS($B:$B;$P2;$K:$K;"Alpa";$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)

Rumus ini cocok untuk membuat dashboard rekap absensi bulanan. Anda dapat mengganti kriteria “Hadir”, “Terlambat”, atau “Alpa” sesuai status yang digunakan di perusahaan.

Contoh Rumus SUMIFS untuk Total Jam Kerja dan Total Lembur

Jika COUNTIFS digunakan untuk menghitung jumlah status, SUMIFS digunakan untuk menjumlahkan angka berdasarkan beberapa kriteria. Dalam absensi, SUMIFS bisa digunakan untuk menjumlahkan total jam kerja atau total jam lembur per karyawan.

Misalnya:

• Kolom B = ID Karyawan.
• Kolom A = Tanggal.
• Kolom M = Jam Kerja.
• Kolom N = Jam Lembur.
• Sel P2 = ID karyawan.
• Sel Q1 = tanggal awal.
• Sel R1 = tanggal akhir.

Menghitung Total Jam Kerja per Karyawan

=SUMIFS($M:$M;$B:$B;$P2;$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)

Menghitung Total Jam Lembur per Karyawan

=SUMIFS($N:$N;$B:$B;$P2;$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)

Jangan lupa ubah format hasil menjadi:

[h]:mm

Format ini penting agar total jam di atas 24 jam tetap tampil benar. Jika tidak memakai format tersebut, Excel bisa menampilkan jam secara tidak sesuai.

Contoh Rumus untuk Menghitung Persentase Kehadiran

Persentase kehadiran membantu HR melihat kedisiplinan karyawan dalam satu periode. Misalnya:

• Jumlah hadir ada di B2.
• Jumlah hari kerja ada di C2.

Rumus Persentase Kehadiran

=IF(C2=0;0;B2/C2)

Ubah format sel menjadi Percentage. Jika B2 berisi 22 dan C2 berisi 25, hasilnya 88%.

Jika perusahaan ingin memasukkan status sakit, izin, atau cuti sebagai kehadiran yang sah, jumlah pembilang dapat disesuaikan. Misalnya hadir + izin + sakit + cuti:

=IF(C2=0;0;(B2+D2+E2+F2)/C2)

Pastikan definisi kehadiran yang dipakai sudah sesuai kebijakan perusahaan agar hasil evaluasi tidak menimbulkan salah paham.

Contoh Rumus IF untuk Menentukan Kategori Disiplin Karyawan

Setelah persentase kehadiran dihitung, HR dapat membuat kategori kedisiplinan. Misalnya:

• Persentase kehadiran 95% ke atas = Sangat Baik.
• Persentase 85% sampai 94% = Baik.
• Persentase di bawah 85% = Perlu Evaluasi.

Jika persentase kehadiran berada di G2:

=IF(G2>=95%;"Sangat Baik";IF(G2>=85%;"Baik";"Perlu Evaluasi"))

Jika Excel Anda tidak menerima tanda persen di rumus, gunakan angka desimal:

=IF(G2>=0,95;"Sangat Baik";IF(G2>=0,85;"Baik";"Perlu Evaluasi"))

Atau dengan pemisah koma:

=IF(G2>=0.95,"Sangat Baik",IF(G2>=0.85,"Baik","Perlu Evaluasi"))

Kategori ini sebaiknya hanya menjadi alat bantu awal. Untuk keputusan HR yang sensitif, perusahaan tetap perlu melihat konteks, seperti sakit, cuti resmi, izin darurat, atau kondisi pekerjaan tertentu.

Contoh Rumus IF untuk Absensi Berdasarkan Shift

Jika perusahaan memiliki beberapa shift, jadwal masuk dan pulang setiap karyawan bisa berbeda. Agar rumus lebih fleksibel, HR dapat membuat tabel master shift.

Contoh tabel master shift:

Untuk mengambil jadwal masuk berdasarkan shift, Anda dapat memakai VLOOKUP atau XLOOKUP.

Rumus VLOOKUP Jadwal Masuk

=VLOOKUP(E2;Master_Shift!$A$2:$C$4;2;FALSE)

Rumus VLOOKUP Jadwal Pulang

=VLOOKUP(E2;Master_Shift!$A$2:$C$4;3;FALSE)

Setelah jadwal masuk dan pulang otomatis terisi, rumus IF untuk keterlambatan dapat tetap digunakan:

=IF(H2="";"Tidak Absen";IF(H2>F2;"Terlambat";"Tepat Waktu"))

Jika perusahaan membutuhkan sistem shift yang lebih praktis, baca artikel software program aplikasi shift kerja dan manfaat sistem HRIS perusahaan di industri manufaktur.

Contoh Rumus IF untuk Menentukan Hari Kerja dan Hari Libur

Dalam absensi, HR perlu membedakan hari kerja, hari libur, dan tanggal merah. Untuk contoh sederhana, jika perusahaan bekerja Senin sampai Jumat, gunakan fungsi WEEKDAY.

Misalnya tanggal berada di A2. Rumus berikut menandai Sabtu dan Minggu sebagai libur:

=IF(WEEKDAY(A2;2)>5;"Libur";"Hari Kerja")

Penjelasan:

• WEEKDAY(A2;2) membuat Senin bernilai 1 dan Minggu bernilai 7.
• Jika hasilnya lebih dari 5, berarti Sabtu atau Minggu.
• Jika tidak, berarti hari kerja.

Jika perusahaan memakai 6 hari kerja, rumus dapat disesuaikan:

=IF(WEEKDAY(A2;2)>6;"Libur";"Hari Kerja")

Untuk tanggal merah nasional, sebaiknya buat daftar libur di sheet terpisah, lalu gunakan COUNTIF untuk mengecek apakah tanggal masuk daftar libur.

=IF(COUNTIF(Libur_Nasional!$A:$A;A2)>0;"Libur Nasional";"Bukan Libur Nasional")

Contoh Rumus IF untuk Menghitung Tunjangan Makan Berdasarkan Kehadiran

Jika perusahaan memberikan tunjangan makan hanya untuk karyawan yang hadir, Excel dapat membantu menghitungnya.

Misalnya:

• K2 = Status Kehadiran.
• B4 = Nominal tunjangan makan per hari.

Rumus Tunjangan Makan Harian

=IF(K2="Hadir";$B$4;0)

Jika perusahaan tetap memberikan tunjangan makan untuk status “Terlambat”, gunakan fungsi OR:

=IF(OR(K2="Hadir";K2="Terlambat");$B$4;0)

Untuk rekap bulanan, jumlahkan hasil tunjangan makan harian atau gunakan COUNTIFS dikalikan tarif tunjangan.

=COUNTIFS($B:$B;$P2;$K:$K;"Hadir";$A:$A;">="&$Q$1;$A:$A;"<="&$R$1)*$B$4

Perhitungan tunjangan seperti ini harus mengikuti kebijakan internal perusahaan. Untuk memahami komponen gaji yang lebih luas, baca artikel komponen gaji dalam sistem pengupahan dan sistem upah ketenagakerjaan di Indonesia.

Contoh Rumus IF untuk Potongan Tidak Hadir Tanpa Keterangan

Jika perusahaan menerapkan potongan untuk alpa, Excel dapat menghitung jumlah potongan berdasarkan jumlah hari alpa.

Misalnya:

• B2 = Jumlah alpa dalam sebulan.
• C2 = Gaji pokok bulanan.
• D2 = Jumlah hari kerja dalam sebulan.

Rumus Potongan Alpa

=IF(B2>0;B2*(C2/D2);0)

Rumus tersebut membagi gaji pokok bulanan dengan jumlah hari kerja, lalu mengalikannya dengan jumlah hari alpa.

Namun, HR tetap perlu memastikan kebijakan potongan gaji dibuat secara jelas, disepakati, dan sesuai aturan perusahaan. Data absensi yang menjadi dasar potongan juga harus valid agar tidak menimbulkan konflik.

Contoh Rumus IF untuk Menandai Karyawan yang Perlu Evaluasi

HR dapat membuat kolom evaluasi berdasarkan jumlah terlambat atau alpa dalam sebulan. Misalnya:

• B2 = Jumlah terlambat.
• C2 = Jumlah alpa.

Jika terlambat lebih dari 3 kali atau alpa minimal 1 kali, statusnya “Perlu Evaluasi”.

=IF(OR(B2>3;C2>=1);"Perlu Evaluasi";"Aman")

Jika menggunakan pemisah koma:

=IF(OR(B2>3,C2>=1),"Perlu Evaluasi","Aman")

Rumus ini hanya alat bantu. Untuk tindakan administratif seperti teguran atau surat peringatan, perusahaan tetap perlu mengikuti prosedur yang berlaku. Baca artikel cara membuat surat peringatan karyawan sesuai aturan.

Contoh Template Rekap Absensi Bulanan

Setelah data harian diolah, HR bisa membuat rekap bulanan dengan kolom berikut:

Rekap seperti ini dapat digunakan sebagai bahan payroll, evaluasi kedisiplinan, perhitungan tunjangan, dan laporan HR bulanan.

Untuk kebutuhan laporan HR yang lebih luas, baca artikel fitur aplikasi HRD terbaik yang wajib ada dan manfaat mengelola administrasi karyawan dengan bantuan teknologi.

Tips Membuat File Absensi Excel yang Rapi

1. Gunakan ID Karyawan yang Unik

Jangan hanya memakai nama karyawan sebagai acuan. Nama bisa sama atau salah ketik. Gunakan ID karyawan agar data lebih akurat saat memakai VLOOKUP, XLOOKUP, COUNTIFS, atau SUMIFS.

2. Buat Sheet Terpisah untuk Master Data

Gunakan sheet terpisah untuk data karyawan, data shift, daftar libur nasional, dan data absensi harian. Struktur seperti ini membuat file lebih mudah dirawat.

3. Gunakan Data Validation

Data Validation dapat membantu membuat dropdown untuk status seperti Hadir, Izin, Sakit, Cuti, dan Alpa. Dengan dropdown, risiko salah ketik berkurang.

4. Gunakan Format Waktu yang Benar

Pastikan jam masuk dan jam pulang disimpan sebagai format waktu, bukan teks. Jika jam tersimpan sebagai teks, rumus perhitungan durasi bisa error atau tidak akurat.

5. Gunakan Format [h]:mm untuk Total Jam

Jika menjumlahkan total jam kerja atau lembur lebih dari 24 jam, gunakan format [h]:mm. Format jam biasa dapat membuat total waktu tampil tidak sesuai.

6. Hindari Menggabungkan Terlalu Banyak Rumus dalam Satu Kolom

Rumus yang terlalu panjang sulit dicek. Lebih baik pecah logika menjadi beberapa kolom bantu, seperti status hadir, status terlambat, durasi terlambat, jam kerja, dan lembur.

7. Kunci Sel Berisi Rumus

Setelah template selesai, proteksi sheet agar rumus tidak terhapus atau berubah tanpa sengaja.

8. Gunakan Conditional Formatting

Conditional Formatting dapat membantu menandai status “Terlambat”, “Alpa”, atau “Cek Data” dengan warna tertentu sehingga HR lebih mudah melakukan review.

9. Simpan Backup Berkala

File absensi adalah data penting. Simpan backup setiap periode agar data tidak hilang jika file rusak atau terhapus.

10. Batasi Akses File Absensi

Data absensi karyawan termasuk data internal perusahaan. Batasi akses hanya untuk HR, finance, manajer terkait, atau pihak yang berwenang.

Kesalahan Umum Saat Menggunakan Rumus IF untuk Absensi

1. Salah Memakai Tanda Pemisah Rumus

Beberapa Excel menggunakan koma, sedangkan sebagian pengaturan regional menggunakan titik koma. Jika rumus tidak berjalan, coba ganti pemisah argumen.

2. Jam Tersimpan sebagai Teks

Jika jam masuk ditulis sebagai teks, Excel tidak dapat membandingkannya dengan benar. Pastikan format sel adalah Time.

3. Tidak Mengunci Sel Referensi

Jika ada sel toleransi seperti B1, gunakan $B$1 agar referensi tidak berubah saat rumus disalin.

4. Rumus Shift Malam Tidak Memperhitungkan Pergantian Hari

Shift malam yang melewati tengah malam membutuhkan rumus khusus. Jika tidak, hasil jam kerja bisa negatif atau salah.

5. Terlalu Banyak Nested IF

Nested IF yang terlalu panjang sulit diaudit. Untuk kondisi yang kompleks, gunakan tabel referensi, IFS, atau kolom bantu.

6. Tidak Memvalidasi Data Sebelum Payroll

Sebelum absensi dipakai untuk menghitung gaji, data harus dicek. Pastikan tidak ada jam masuk kosong, jam pulang kosong, tanggal salah, atau status tanpa keterangan.

7. Tidak Menyesuaikan Kebijakan Perusahaan

Setiap perusahaan bisa memiliki kebijakan absensi yang berbeda. Rumus Excel harus mengikuti kebijakan internal yang berlaku, bukan sekadar meniru template umum.

8. Menggunakan Excel untuk Proses yang Sudah Terlalu Kompleks

Jika jumlah karyawan banyak, shift kompleks, lembur sering berubah, dan data payroll harus terintegrasi, Excel mungkin mulai kurang efisien. Dalam kondisi seperti ini, perusahaan dapat mempertimbangkan sistem absensi online atau HRIS.

Kapan Perusahaan Perlu Beralih dari Excel ke Absensi Online?

Excel masih berguna untuk perusahaan kecil atau kebutuhan sederhana. Namun, ada kondisi ketika absensi online lebih praktis dan aman.

Perusahaan sebaiknya mulai mempertimbangkan absensi online jika:

• Jumlah karyawan semakin banyak.
• Perusahaan memiliki banyak lokasi kerja.
• Shift kerja sering berubah.
• Data lembur harus disetujui berlapis.
• HR sering memperbaiki rumus absensi.
• Data absensi sering terlambat dikumpulkan.
• Payroll membutuhkan data kehadiran yang lebih real-time.
• Karyawan sering lupa absen masuk atau pulang.
• Perusahaan membutuhkan GPS, selfie, atau validasi lokasi.
• Manajemen membutuhkan laporan otomatis.

Sistem absensi online dapat membantu mengurangi input manual, mempercepat rekap data, dan mengintegrasikan kehadiran dengan payroll. Untuk referensi, baca artikel aplikasi absensi online gratis dan berbayar, pentingnya aplikasi absensi terintegrasi fingerprint, dan pentingnya mencoba software HRD gratis sebelum membeli.

Checklist Rumus Absensi Excel untuk HRD

Sebelum file absensi digunakan secara rutin, HR dapat memakai checklist berikut:

• Data karyawan sudah memiliki ID unik.
• Format tanggal sudah konsisten.
• Format jam masuk dan pulang sudah memakai format waktu.
• Sheet master shift sudah dibuat jika perusahaan memakai shift.
• Daftar libur nasional sudah tersedia jika diperlukan.
• Rumus status hadir sudah berjalan.
• Rumus terlambat sudah memperhitungkan toleransi.
• Rumus pulang cepat sudah tersedia.
• Rumus jam kerja sudah memperhitungkan jam istirahat.
• Rumus shift malam sudah memperhitungkan pergantian hari.
• Rumus lembur sudah sesuai kebijakan perusahaan.
• COUNTIFS untuk rekap bulanan sudah benar.
• SUMIFS untuk total jam kerja dan lembur sudah benar.
• Format total jam memakai [h]:mm.
• Kolom validasi data sudah tersedia.
• Conditional Formatting sudah digunakan untuk status penting.
• Sel rumus sudah diproteksi.
• File backup sudah disiapkan.
• Akses file sudah dibatasi.

Contoh Kombinasi Rumus Absensi Lengkap

Berikut contoh kombinasi rumus yang dapat digunakan dalam satu tabel absensi harian.

Rumus di atas dapat disesuaikan dengan struktur file masing-masing perusahaan. Jika posisi kolom berbeda, ubah referensi sel sesuai tabel yang digunakan.

FAQ tentang Rumus Excel IF untuk Absensi Karyawan

Apa fungsi IF dalam absensi karyawan?

Fungsi IF digunakan untuk membuat keputusan otomatis berdasarkan kondisi tertentu. Dalam absensi, IF dapat digunakan untuk menentukan status hadir, terlambat, alpa, izin, sakit, cuti, pulang cepat, atau lembur.

Apa rumus IF untuk menentukan terlambat?

Jika jadwal masuk ada di F2 dan jam masuk aktual ada di H2, rumusnya:

=IF(H2="";"Tidak Absen";IF(H2>F2;"Terlambat";"Tepat Waktu"))

Bagaimana rumus IF jika ada toleransi keterlambatan?

Jika toleransi berada di B1, gunakan:

=IF(H2="";"Tidak Absen";IF(H2>F2+$B$1;"Terlambat";"Tepat Waktu"))

Bagaimana cara menghitung durasi terlambat di Excel?

Gunakan rumus:

=IF(H2="";0;IF(H2>F2;H2-F2;0))

Lalu ubah format sel menjadi [h]:mm.

Bagaimana cara menghitung jam kerja shift malam?

Gunakan rumus yang memperhitungkan pergantian hari:

=IF(OR(H2="";I2="");0;IF(I2<H2;I2+1-H2;I2-H2))

Apa bedanya COUNTIF dan COUNTIFS?

COUNTIF digunakan untuk menghitung data berdasarkan satu kriteria, sedangkan COUNTIFS digunakan untuk menghitung data berdasarkan beberapa kriteria, misalnya ID karyawan, periode, dan status kehadiran.

Apa fungsi SUMIFS dalam absensi?

SUMIFS digunakan untuk menjumlahkan data dengan beberapa kriteria. Dalam absensi, SUMIFS bisa dipakai untuk menghitung total jam kerja atau total lembur per karyawan dalam periode tertentu.

Kenapa hasil perhitungan jam di Excel salah?

Biasanya karena format jam tersimpan sebagai teks, format total jam belum memakai [h]:mm, atau rumus belum memperhitungkan shift yang melewati tengah malam.

Apakah Excel cukup untuk absensi perusahaan?

Excel cukup untuk absensi sederhana atau jumlah karyawan kecil. Namun, jika perusahaan memiliki banyak karyawan, banyak lokasi, banyak shift, dan integrasi payroll, sistem absensi online atau HRIS biasanya lebih efisien.

Apakah data absensi bisa langsung dipakai untuk payroll?

Bisa, tetapi harus divalidasi terlebih dahulu. Pastikan tidak ada data kosong, jam salah, status tidak konsisten, atau approval lembur yang belum selesai.

Kesimpulan

Rumus Excel IF untuk absensi karyawan sangat membantu HRD dalam mengelola data kehadiran secara lebih cepat dan rapi. Dengan IF, HR dapat membuat status otomatis seperti hadir, terlambat, izin, sakit, cuti, alpa, pulang cepat, hingga lembur.

Selain IF, fungsi lain seperti COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS, VLOOKUP, XLOOKUP, OR, AND, dan WEEKDAY juga dapat digunakan untuk membuat sistem absensi Excel yang lebih lengkap. Rumus-rumus ini membantu HR membuat rekap bulanan, menghitung total jam kerja, menghitung jumlah terlambat, menghitung total lembur, dan memvalidasi data absensi sebelum digunakan untuk payroll.

Meski demikian, Excel tetap memiliki keterbatasan. Rumus bisa rusak, data bisa salah input, dan file bisa sulit dikelola jika jumlah karyawan semakin banyak. Karena itu, perusahaan perlu memastikan struktur tabel rapi, format waktu benar, rumus diproteksi, dan data divalidasi sebelum dipakai untuk menghitung gaji.

Jika kebutuhan absensi sudah semakin kompleks, perusahaan dapat mempertimbangkan sistem absensi online atau HRIS agar data kehadiran, shift, cuti, lembur, dan payroll bisa terintegrasi dengan lebih efisien.

Referensi External

• Microsoft Support – IF Function
• Microsoft Support – COUNTIFS Function
• Microsoft Support – SUMIFS Function
• Microsoft Support – IFERROR Function
• Microsoft Support – WEEKDAY Function
• Microsoft Support – Use Nested Functions in an Excel Formula
• BPK RI – PP No. 35 Tahun 2021 tentang PKWT, Alih Daya, Waktu Kerja dan Waktu Istirahat, dan PHK
• Peraturan.go.id – PP No. 35 Tahun 2021
• BPK RI – Dokumen PDF PP Nomor 35 Tahun 2021