Cara Membuat Tabel Absensi Pekerja Harian di Excel dengan Conditional Formatting

Mencatat absensi pekerja adalah tugas rutin yang memerlukan ketelitian, terutama dalam membedakan antara hari kerja efektif dan hari libur. Excel menyediakan berbagai alat yang bisa membantu Anda mengelola absensi pekerja dengan lebih mudah dan efisien. Salah satu alat yang sangat berguna adalah Conditional Formatting, yang memungkinkan Anda untuk menandai sel berdasarkan kriteria tertentu. Dalam artikel ini, kita akan membahas langkah-langkah membuat tabel absensi pekerja harian di Excel menggunakan Conditional Formatting.

1. Menyiapkan Data Awal

Biar lebih mudah, template yang akan kita buat untuk Absensi Pekerja Harian di Excel ini adalah sebagai berikut:

Sebelum memulai dengan Conditional Formatting, pastikan Anda telah menyiapkan data berikut di Excel:

• Nomor Urut Pekerja.
• Nama Pekerja di kolom C.
• Tanggal Mulai dan Tanggal Akhir kerja pada kolom D dan E.
• Hari Libur Nasional di kolom C (misalnya di sel C12 hingga C14).
• Tabel dengan tanggal pada baris ke-2 (misalnya dari F2 hingga AJ2).
• Kolom Jumlah Hari Efektif Kerja di kolom AK.
• Kolom Jumlah presensi di kolom AL.
• Terakhir, Jumlah Absen di kolom AM.

2. Menandai Hari Libur dengan Conditional Formatting

Untuk menandai hari libur dalam absensi pekerja harian di Excel, Anda bisa menggunakan rumus Conditional Formatting berikut:

=AND(AND(F$2>=$D3;F$2<=$E3);OR(F$2=$C$12:$C$14;WEEKDAY(F$2;3)>5))

Langkah-langkahnya:

• Pilih rentang tanggal dalam tabel (misalnya F3:AJ10).
• Buka tab Home di Excel, lalu klik Conditional Formatting > New Rule.
• Pilih Use a formula to determine which cells to format.
• Masukkan rumus di atas ke dalam kolom formula.
• Tentukan format yang ingin Anda gunakan untuk menandai hari libur, seperti memberi warna merah.

Rumus ini bekerja dengan cara memeriksa apakah tanggal di baris ke-2 (misalnya F2) termasuk dalam rentang tanggal kerja (D3:E3) dan apakah tanggal tersebut merupakan hari libur atau akhir pekan (Sabtu dan Minggu).

Penjelasan Rumus

Rumus conditional formatting berikut:

=AND(AND(F$2>=$D3;F$2<=$E3);OR(F$2=$C$12:$C$14;WEEKDAY(F$2;3)>5))

digunakan untuk menandai hari libur dalam tabel absensi pekerja harian di Excel. Berikut adalah penjelasan dari masing-masing bagian rumus ini:

1. Bagian Utama: AND( ... )

• Fungsi AND: Fungsi AND akan mengembalikan nilai TRUE hanya jika semua kondisi di dalamnya terpenuhi (bernilai TRUE). Jika ada satu saja kondisi yang bernilai FALSE, maka hasilnya akan FALSE.
• Pada rumus ini, ada dua fungsi AND yang bekerja bersama-sama.

2. Bagian Pertama: AND(F$2>=$D3;F$2<=$E3)

• F$2>=$D3: Mengecek apakah tanggal di sel F2 (hari yang akan diperiksa) sama dengan atau setelah tanggal mulai kerja di D3.
• F$2<=$E3: Mengecek apakah tanggal di sel F2 sama dengan atau sebelum tanggal akhir kerja di E3.
• Kesimpulan: Bagian ini memastikan bahwa tanggal di F2 berada di antara tanggal mulai (D3) dan tanggal akhir (E3). Ini berarti bahwa tanggal di F2 adalah hari kerja yang valid.

3. Bagian Kedua: OR(F$2=$C$12:$C$14;WEEKDAY(F$2;3)>5)

• Fungsi OR: Fungsi OR akan mengembalikan nilai TRUE jika setidaknya salah satu dari kondisi di dalamnya terpenuhi (bernilai TRUE). Jika semua kondisi bernilai FALSE, maka hasilnya akan FALSE.
• F$2=$C$12:$C$14: Mengecek apakah tanggal di F2 cocok dengan salah satu tanggal yang ada di daftar hari libur (misalnya di rentang C12:C14).
• WEEKDAY(F$2;3)>5: Fungsi WEEKDAY mengembalikan angka yang mewakili hari dalam minggu untuk tanggal tertentu. Dengan parameter 3, hari Senin dikembalikan sebagai 0, Selasa sebagai 1, dan seterusnya, sampai Minggu yang dikembalikan sebagai 6. Kondisi WEEKDAY(F$2;3)>5 berarti bahwa F2 adalah hari Sabtu (5) atau Minggu (6).
• Kesimpulan: Bagian ini memastikan bahwa F2 adalah hari libur, baik karena tanggalnya ada di daftar hari libur nasional (C12:C14) atau karena tanggal tersebut adalah akhir pekan (Sabtu atau Minggu).

4. Gabungan: AND(AND(...); OR(...))

• AND di bagian luar: Menggabungkan dua bagian di atas. Rumus ini hanya akan menghasilkan TRUE jika tanggal di F2 adalah hari kerja yang valid (dalam rentang D3:E3) dan tanggal tersebut adalah hari libur (baik karena ada di daftar hari libur atau merupakan akhir pekan).
• Hasilnya: Jika semua kondisi di dalam rumus terpenuhi, maka sel tersebut akan diberi format sesuai dengan yang telah Anda tentukan di Conditional Formatting (misalnya, diwarnai merah untuk menandai hari libur).

Rumus ini sangat berguna untuk menandai hari-hari libur secara otomatis dalam tabel absensi pekerja harian di Excel, membantu memisahkan antara hari libur dan hari kerja biasa dengan jelas.

3. Menandai Hari Efektif Kerja

Selanjutnya, untuk menandai hari kerja efektif (hari kerja yang bukan hari libur atau akhir pekan), gunakan rumus berikut:

=AND(F$2>=$D3;F$2<=$E3)

Langkah-langkahnya sama seperti pada langkah sebelumnya, tetapi kali ini, gunakan rumus yang berbeda untuk menandai hari efektif kerja dengan format yang berbeda, misalnya warna hijau.

Dan berikut adalah hasilnya:

4. Menghitung Hari Efektif Kerja

Setelah tabel absensi pekerja harian di Excel ditandai dengan Conditional Formatting, Anda bisa menghitung jumlah hari kerja efektif menggunakan rumus berikut:

=NETWORKDAYS.INTL(D3;E3;11;$C$12:$C$14)

Rumus ini menghitung jumlah hari kerja antara tanggal mulai (D3) dan tanggal akhir (E3), dengan mengabaikan akhir pekan dan hari libur nasional yang sudah ditentukan di kolom C12:C14. Hasilnya akan muncul di sel AK3.

Penjelasan Rumus

Rumus Excel diatas digunakan untuk menghitung jumlah hari kerja efektif antara dua tanggal, yaitu Tanggal Mulai (D3) dan Tanggal Akhir (E3), dengan mempertimbangkan hari libur dan akhir pekan.

Berikut adalah penjelasan dari setiap komponen rumus ini:

1. Fungsi NETWORKDAYS.INTL:

• Fungsi NETWORKDAYS.INTL digunakan untuk menghitung jumlah hari kerja antara dua tanggal, dengan opsi untuk menyesuaikan akhir pekan dan mengabaikan hari libur tertentu.
• Tidak seperti fungsi NETWORKDAYS biasa, NETWORKDAYS.INTL memungkinkan Anda untuk menentukan pola akhir pekan yang berbeda (misalnya, Jumat-Sabtu, Sabtu-Minggu, atau bahkan hanya Minggu).

2. D3 dan E3:

• D3: Ini adalah tanggal mulai (start_date) yang merupakan awal dari rentang tanggal yang ingin dihitung.
• E3: Ini adalah tanggal akhir (end_date) yang merupakan akhir dari rentang tanggal yang ingin dihitung.

3. 11 (Weekend parameter):

• Argumen ketiga (11) menentukan pola akhir pekan. Dalam konteks rumus ini, angka 11 berarti hanya hari Minggu yang dihitung sebagai akhir pekan.
• Dalam fungsi NETWORKDAYS.INTL, Anda bisa menggunakan berbagai kode untuk menentukan hari-hari yang dianggap sebagai akhir pekan:
  • 1 = Sabtu dan Minggu (default)
  • 2 = Minggu dan Senin
  • 11 = Hanya Minggu
  • 12 = Hanya Senin
  • Dan sebagainya.

4. $C$12:$C$14 (Hari Libur):

• Argumen keempat adalah daftar hari libur (holidays). Dalam rumus ini, rentang $C$12:$C$14 berisi daftar tanggal yang dianggap sebagai hari libur.
• Setiap tanggal dalam rentang ini akan dikecualikan dari perhitungan hari kerja. Artinya, meskipun tanggal tersebut jatuh pada hari kerja (misalnya, Senin-Jumat), jika termasuk dalam daftar libur, tanggal tersebut tidak akan dihitung sebagai hari kerja.

5. Menghitung Jumlah Kehadiran Pekerja

Untuk menghitung jumlah kehadiran pekerja, gunakan rumus berikut:

=SUM((F$2:AJ$2>=$D3)*(F$2:AJ$2<=$E3)*(F3:AJ3="M"))

Rumus ini akan menjumlahkan kehadiran pekerja pada hari kerja efektif yang ditandai dengan huruf “M” (Misalnya “M” mewakili “Masuk” atau hadir). Hasil perhitungan ini akan muncul di sel AL3. Berikut hasilnya:

Penjelasan Rumus

Rumus Excel di atas digunakan untuk menghitung jumlah kehadiran pekerja selama rentang tanggal tertentu, dengan syarat tertentu pada setiap sel.

Berikut adalah penjelasan dari setiap komponen rumus ini:

1. Bagian (F$2:AJ$2>=$D3):

• F$2:AJ$2>=$D3: Mengecek apakah setiap tanggal dalam rentang F2 hingga AJ2 lebih besar atau sama dengan tanggal mulai kerja di D3.
• Hasilnya: Ini akan menghasilkan sebuah array (daftar nilai) yang terdiri dari TRUE (jika tanggal di baris F2:AJ2 ≥ D3) atau FALSE (jika tanggal di baris F2:AJ2 < D3).

2. Bagian (F$2:AJ$2<=$E3):

• F$2:AJ$2<=$E3: Mengecek apakah setiap tanggal dalam rentang F2 hingga AJ2 lebih kecil atau sama dengan tanggal akhir kerja di E3.
• Hasilnya: Ini juga menghasilkan sebuah array (daftar nilai) yang terdiri dari TRUE (jika tanggal di baris F2:AJ2 ≤ E3) atau FALSE (jika tanggal di baris F2:AJ2 > E3).

3. Bagian (F3:AJ3="M"):

• F3:AJ3="M": Mengecek apakah setiap sel dalam rentang F3 hingga AJ3 berisi huruf “M”.
• Biasanya, “M” mungkin digunakan untuk menandakan bahwa pekerja “Masuk” atau hadir pada hari tersebut.
• Hasilnya: Bagian ini menghasilkan array yang berisi TRUE jika sel tersebut berisi “M” dan FALSE jika tidak.

4. Perkalian Array ( ... )*( ... )*( ... ):

• Ketiga bagian di atas menghasilkan tiga array yang masing-masing berisi TRUE atau FALSE.
• Dalam Excel, TRUE dianggap sebagai 1 dan FALSE sebagai 0.
• Dengan mengalikan ketiga array ini, hanya elemen-elemen di mana ketiga kondisi bernilai TRUE (atau 1) yang akan menghasilkan nilai 1. Jika salah satu kondisi bernilai FALSE (atau 0), hasilnya adalah 0.

5. Fungsi SUM:

• Fungsi SUM kemudian menjumlahkan semua nilai 1 yang dihasilkan oleh perkalian array.
• Ini berarti rumus menghitung jumlah hari di mana:
  1. Tanggal berada dalam rentang tanggal kerja (antara D3 dan E3).
  2. Pekerja tercatat hadir (dengan tanda “M” di baris F3:AJ3).

6. Menghitung Jumlah Ketidakhadiran Pekerja

Untuk menghitung jumlah ketidakhadiran pekerja, gunakan rumus berikut di sel AM3:

=AK3-AL3

Copy dan paste ke sel-sel yang ada di bawahnya. Berikut hasilnya:

Kesimpulan

Dengan menggunakan Conditional Formatting dan beberapa rumus Excel yang telah dibahas di atas, Anda dapat dengan mudah membuat tabel absensi pekerja harian di Excel yang akurat dan efektif. Tabel ini tidak hanya memudahkan pencatatan kehadiran pekerja, tetapi juga memberikan gambaran jelas tentang hari kerja efektif dan hari libur. Dengan pengaturan yang tepat, manajemen absensi akan menjadi lebih sistematis dan efisien.

Pranala Luar

File Excel Cara Membuat Tabel Absensi Pekerja Harian di Excel dengan Conditional Formatting ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul: Cara Membuat Tabel Absensi Pekerja Harian di Excel dengan Conditional Formatting. Di sana Anda akan melihat bagaimana rumusnya bekerja.

—