Menghitung Lembur Karyawan, Cara Menghitung Lembur Berdasarkan Absensi Dengan Rumus Excel

Cara Menghitung Lembur Berdasarkan Absensi Dengan Rumus Excel - Jika kita akan menghitung gaji karyawan pasti akan menemukan kebutuhan dalam pengolahan data lembur.

Lembur yang dimaksud adalah kelebihan jam kerja pada karyawan yang dilakukan pada hari kerja, hari libur (minggu) atau hari libur nasional.

Tuntutan penyelesaian sebuah pekerjaan atau terkait dengan target biasanya perusahaan mewajibkan karyawannya untuk lembur.

Tentu kewajiban yang diberikan oleh perusahaan tersebut juga telah disetujui oleh karyawan yang bersangkutan.

Dalam artikel sebelumnya telah dibahas tentang aturan dan tata cara penghitungan lembur.

Ada baiknya silahkan pelajari terlebih dahulu sebelum kita melanjutkan pembahasan artikel ini.

Dalam artikel ini saya akan membahas tentang cara menghitung lembur berdasarkan absensi yang dilakukan oleh karyawan setiap harinya.

Rekap absensi ini merupakan langkah awal dalam penghitungan gaji, sehingga artikel ini akan menjadi bagian yang tidak bisa dipisahkan pada artikel selanjutnya yang membahas tentang cara menghitung gaji karyawan dalam Microsoft Excel.

Cara Menghitung Lembur Berdasarkan Absensi

Dalam artikel yang membahas tentang ketetapan lembur telah disebutkan bahwa lembur untuk perusahaan yang memberlakukan 5 hari kerja (Sabtu & Minggu) libur dan juga perusahaan yang memberlakukan 6 hai kerja (Minggu) Libur sedikit berbeda.

Dalam artikel ini saya akan membahas tentang lembur untuk perusahaan yang memberlakukan 6 hari kerja.

Penting untuk diperhatikan bahwa jika perusahaan tempat Anda bekerja memberlakukan 5 hari kerja maka rumus ini harus diedit terlenih dahulu untuk menyesuaikan perhitungan total jam lemburnya.

Ketetapan lembur Perusahaan dengan 6 hari kerja

Untuk perusahaan yang memberlakukan 6 hari kerja mempunyai beberapa ketetapan sebagai berikut :

Lembur pada hari kerja ratenya adalah : 1.5x upah sejam untuk 1 jam pertama.
Lembur pada hari libur (Minggu) atau libur nasional : 2x upah sejam untuk 7 jam pertama, 3x upah sejam untuk 1 jam berikutnya (jam ke-8) dan 4x upah sejam untuk jam berikutnya (jam ke-9 dan ke-10).
Hari libur yang mempunyai jam terpendek (hari Jumat) : 2x upah sejam untuk 5 jam pertama, 3x upah sejam untuk 1 jam berikutnya (jam ke-6) dan 4x upah untuk sisanya atau jam ke-7 dan ke-8.

Ketetapan tersebut diatas telah saya buatkan dalam bentuk rumus sehingga kita tidak perlu untuk memisahkannya lagi.

Salah satu yang penting dalam proses penghitungan ini adalah PPH 21, tentang penghitungan PPH 21 ini telah saya bahas pada artikel yang lain dan silahkan untuk dipelajari.

Setelah mengetahui ketetapan apa saja yang ada untuk menentukan lembur pada perusahaan yang mempunyai 6 hari kerja maka selanjutnya kita akan melihat cara penghitungan tersebut dalam Microsoft Excel.

Menghitung Lembur Berdasarkan Absensi Dalam Excel

Dalam perhitungan ini saya bagi menjadi dua bagian utama, yaitu penghitungan gaji dan penghitungan Lembur.

Untuk mendukung rumus - rumus yang telah saya buatkan hal pertama yang harus dilakukan adalah membuat master Data Karyawan.

Data Karyawan ini akan menjadi data utama untuk pengisian gaji karyawan serta akan menjadi dasar utama juga dalam menghitung gaji yang akan saya bahas selanjutnya.

Untuk Tabel Data Karyawan ini pada nama sheetnya yang semua Sheet1 silahkan ganti dengan nama DATA KRY.

Jadi, dalam file Excel tersdiri dari 4 sheet yaitu Data karyawan, Absensi (penghitungan lembur), Gaji Karyawan (penghitungan gaji) serta Slip gaji (untuk mencetak slip gaji).

Masing - masing sheet akan saya bahas dalam artikel tersendiri kecuali Data karyawan akan digabungkan dalam artikel ini karena tidak menggunakan rumus atau hanya input manual saja.

Setelah membuat Tabel Data Karyawan maka selanjutnya kita akan membuat format untuk menghitung lembur dengan rumus Excel.

Format tersebut didasarkan pada absensi sehingga jika ada karyawan yang bekerja melebihi jam normalnya maka otomatis akan dihitung lembur.

Untuk menghitung lemburnya buatlah format seperti gambar dibawah ini pada Sheet Excel selanjutnya :

Ada dua bagian utama dalam sheet tersebut yaitu sebelah kiri merupakan perhitungan gaji dan sebelah kanan merupakan perhitungan jam lembur berdasarkan ratenya.

Untuk detail penjelasan rumus yang digunakan pada form tersebut akan saya buatkan dalam artikel tersendiri supaya artikel ini tidak terlalu panjang.

Dan jika diperhatikan hampir semua dari pos baris maupun kolom tersebut diberikan rumus sehingga kita hanya perlu mengisikan ID karyawan dan juga jam masuk dan keluar dari masing - masing karyawan.

Jika Anda menguasai penggunaan rumus - rumus terutama rumus IF akan saya jelaskan secara singkat tentang rumus yang digunakan untuk mencari rate lembur dalam kolom P, Q, R dan S.

Sekali lagi saya sampaikan untuk detail pembahasan rumus akan saya buatkan artikel tersendiri, mohon bersabar dan menunggu.

- Kolom P atau rate lembur 1,5x

Untuk kolom ini akan menghasilakn jam lembur 1 jam pertama atau kurang dari 1 jam pertama dan ini akan kosong jika lembur pada hari minggu atau hari libur nasional sesuai dengan ketetapan lembur yang telah saya sampaikan.

Untuk menggunakannya masukan rumus sebagai berikut :

Rumus Kolom P :

=IF(AND(J4<>"Minggu";O4>1;K4<>"Ya";L4<>"");1;IF(AND(J4<>"Minggu";O4<=1;K4<>"Ya";L4<>"");O4;0))

Dengan rumus tersebut maka otomatis akan memunculkan jam lembur yang dilakukan oleh karyawan .

- Kolom Q atau rate lembur 2x

Untuk kolom ini akan menghitung jam lembur karyawan dari sisa jam lembur dikurangi dengan 1 jam pertama jika dilakukan pada hari kerja.

Kriteria kedua ini akan memunculkan jam kerja dengan total 7 jam pertama jika lembur dilakukan pada hari libur atau libur nasional.

Rumusnya adalah sebagai berikut :

Rumus Kolom Q :

=IF(AND(J4<>"Minggu";O4>1;K4<>"Ya";L4<>"");O4-P4;IF(AND(J4<>"Minggu";J4<>"Jumat";O4>7;K4="Ya";L4<>"");7;IF(AND(J4<>"Minggu";J4<>"Jumat";O4<=7;K4="Ya";L4<>"");O4;IF(AND(J4="Minggu";O4<=7;L4<>"");O4;IF(AND(J4="Minggu";O4>7;L4<>"");7;IF(AND(J4="Jumat";O4<=5;L4<>"");O4;IF(AND(J4="Jumat";O4>5;L4<>"");5;0)))))))

Dengan rumus tersebut maka akan muncul jam lembur yang memenuhi kriteria untuk rate 2x.

- Kolom R atau rate lembur 3x

Kolom ini akan menghitung lembur karyawan yang lebih dari 7 jam pada hari libur. Jika lembur tersebut lebih dari 7 jam tapi dibawah 8 jam maka yang akan muncul adalah sisa jam tersebut.

Tetapi jika lembur tersebut lebih dari 8 jam maka yang akan muncul adalah 1 atau jam ke-8.

Rumusnya adalah sebagai berikut :

Rumus Kolom R :

=IF(AND(J4<>"Minggu";J4<>"Jumat";O4<=8;K4="Ya";L4<>"");O4-Q4;IF(AND(J4<>"Minggu";J4<>"Jumat";O4>8;K4="Ya";L4<>"");1;IF(AND(J4="Minggu";O4<=8;L4<>"");O4-Q4;IF(AND(J4="Minggu";O4>8;L4<>"");1;IF(AND(J4="Jumat";K4="Ya";O4<=6;L4<>"");O4-Q4;IF(AND(J4="Jumat";K4="Ya";O4>6;L4<>"");1;0))))))

Pada rate ini yang lembur pada hari kerja biasa sudah tidak memenuhi kriteria atau sudah dihitung pada rate 1,5 dan 2.

- Kolom S atau rate lembur 4x

Ini merupakan rate terakhir dan paling besar, dalam kriteria ini akan menghitung sisa dari jam lembur yang telah dpotong sebelumnya.

Rumusnya adalah sebagai berikut :

Rumus Kolom S :

=IF(AND(J4<>"Minggu";J4<>"Jumat";K4="Ya";L4<>"";O4>8);O4-Q4-R4;IF(AND(J4="Mingu";L4<>"";O4>8);O4-Q4-R4;IF(AND(J4="Jumat";K4="Ya";L4<>"";O4>6);O4-Q4-R4;0)))

Setelah menghitung ini maka proses penghitungan rate lembur telah selesai dan selanjutnya kita akan menghitung total jam lembur seluruhnya pada hari tersebut.

- Kolom T atau Total Jam Lembur

Kolom ini akan menghitung seluruh jam lembur setelah dikaliakan dengan rate yang tadi kita hitng dalam kolom sebelumnya.

Untuk kolom ini rumusnya adalah sebagai berikut :

Rumus Kolom T :

=(P4*$P$3)+(Q4*$Q$3)+(R4*$R$3)+(S4*$S$3)

Setelah menghitung total lembur pada hari tersebut maka kita sudah bisa mendapatkan seluruh total jam karyawan yang bersangkutan yang akan dimasukan ke dalam cell C8 yang akan dikalikan dengan upah lembur per jamnya.

Setelah kita memasang semua rumus pada kolom tersebut diatas maka selanjutnya yang tidak kalah penting adalah bagaimana cara mengisi form lembur tersebut ?

Untuk mengisinya ikuti langkah - langkah berikut ini :

Isikan ID Karywan sesuai dengan yang diinput pada sheet Data Karyawan, maka otomatis kolom lainnya akan terisi.
Isi kolom I atau Tanggal, cukup dengan mengisikan satu cell saja untuk tanggal 1.
Jika terdapat hari libur nasional maka pada kolom K yang sudah terdapat pilihan dropdown list pilih "Ya". Jika bukan hari libur nasional pilih "Tidak" atau cukup kosongkan saja.
Isi jam kerja masuk dan keluar karyawan pada kolom L dan M.

Perhatian !!! format untuk menghitung ini menggunakan hari dengan ejaan Indonesia, jadi untuk menggunakannya silahkan setting regional komputer Anda menjadi Indonesia.

Setlah 4 langkah tersebut diatas diisi atau dilakukan maka otomatis lembur dan gaji karyawan akan terhitung.

Dalam menentukan rate jam lembur Saya menggunakan gabungan rumus IF dan AND, untuk Anda yang ingin mempelajarinya silahkan baca dalam kelompok Fungsi Rumus Logika.

Seperti yang saya sebutkan diatas bahwa detail tentang penggunaan rumus akan saya bahasa dalam artikel tersendiri supaya artikel ini tidak terlalu panjang.

Butuh contoh filenya ?

Untuk contoh filenya akan saya sertakan dalam pembahasan menghitung gaji karyawan yang merupakan satu kesatuan pembahasan dengan artikel ini. Silahkan buka dalam artikel dibawah ini :

Baca : Menghitung Gaji, Cara Menghitung Gaji Dengan Rumus Dalam Microsoft Excel

Itulah pembahasan tentang cara menghitung lembur berdasarkan absensi dalam Microsoft Excel.

Jika pembaca merasa ada rumus atau hal lain yang perlu diluruskan mari kita diskusikan melalui kolom komentar dibawah artkel ini.

Semoga artikel ini bermanfaat untuk semua pembaca dan jika berkenan bagikanlah artikel ini kepada orang lain yang mungkin sedang membuthkan solusi dengan penghitungan lembur.