Perhitungan lembur memang kadang dipandang sesuatu yang sangat sulit. Padalah perhitungan lembur tersebut bisa dilakukan dengan sangat mudah apalagi jika dilakukan dengan menggunakan Microsoft Excel.
Cara menghitung lembur dalam Excel akan mengikuti dasar serta pola penghitungan lembur yang memang berlaku di Negara kita.
Hanya saja prosesnya memang akan menggunakan bantuan beberapa rumus Excel supaya perhitungan lembur bisa berjalan secara otomatis.
Cara Menghitung Lembur Dengan Menggunakan Rumus Excel
Pada dasarnya untuk menghitung lembur kita hanya akan mengurangi jam masuk bekerja dengan jam pulang bekerja.
Selanjutnya dari kedua jam tersebut akan didapatkan selisih jam dan selisih jam inilah yang akan kita kalikan dengan kelipatan lembur.
Dalam Excel terkait dengan format tanggal dan waktu sudah disediakan termasuk rumus untuk menghitung selisih jam tersebut.
Baca Juga : Menghitung Selisih Jam Untuk Dasar Perhitungan Lembur Dalam Excel
Tapi sebelum membahas cara menghitung lembur kita akan melihat dasar – dasar perhitungan lembur yang berlaku di Negara kita.
1. Dasar Perhitungan Lembur Karyawan
Saya akan bahas sedikit terkait dengan dasar – dasar penghitungan lembur Karyawan.
Dasar – dasar perhitungan lembur ini akan dijadikan batasan pengunaan rumus yang nanti akan kita gunakan.
Dasar perhitungan lembur ini dibagi menjadi tiga bagian, yaitu sebagai berikut :
- Perhitungan Lembur Hari Biasa
Dasar perhitungan lembur yang pertama adalah lembur untuk hari biasa atau di Negara kita biasanya antara hari Senin sampai dengan Jum’at atau Sabtu.
Adapun perhitungan lembur untuk hari biasa adalah sebagai berikut :
– 1 Jam pertama dikalikan dengan 1,5
– Jam ke-2, ke-3 dan seterusnya dikalikan dengan 2Perhitungan lembur diatas berlaku untuk jam kerja dengan jumlah hari 6 dalam seminggu atau 40 Jam dalam Waktu satu minggu dan berlaku juga untuk hari kerja dengan jumlah 5 hari dalam seminggu.
- Perhitungan Lembur Hari Libur
Pada dasarnya untuk menghitung lembur hari libur ini bisa dibagi menjadi dua kondisi yaitu hari libur bekerja misalnya hari Minggu atau hari biasa tetapi statusnya libur nasional atau tanggal merah.
Adapun perhitungan lembur untuk hari libur adalah sebagai berikut :
– 7 Jam pertama dikalikan dengan 2
– 1 Jam berikutnya atau jam ke-8 dikalikan dengan 3
– Jam ke-9, Ke-10 dan selanjutnya dikalikan dengan 4Dalam perhitungan lembur hari libur ini tidak ada kelipatan 1.5 tetapi langsung dikalikan dengan kelipatan 2.
Selanjutnya untuk dasar perhitungan lembur hari libur diatas berlaku untuk jumlah hari kerja 6 hari atau 40 jam dalam waktu 1 Minggu.
Jika jumlah hari kerja adalah 5 hari dalam seminggu maka perhitungan lemburnya adalah sebagai berikut :
– 8 Jam pertama dikalikan 2
– 1 Jam berikutnya atau jam ke-9 dikalikan dengan 3
– Jam ke-10, Ke-11 dan selanjutnya dikalikan dengan 4Jadi, mohon dijadikan catatan bahwa perhitungan lembur untuk hari libur ini dibagi dua sesuai dengan jumlah hari kerjanya.
- Perhitungan Lembur Hari Libur Terpendek
Dasar perhitungan lembur yang ketiga adalah untuk lembur dengan jumlah hari yang pendek, misalnya libur pada hari Jum’at.
Adapun perhitungan lemburnya adalah sebagai berikut :
– 5 Jam pertama dikalikan 2
– 1 Jam berikutnya atau jam ke-6 dikalikan dengan 3
– Jam ke-7, 8 dan selanjutnya dikalikan dengan 4
Cukup kompleks memang dasar perhitungan lembur diatas tetapi jangan khawatir rumus Excel yang akan kita gunakan untuk menghitung lembur tidak akan sesulit yang kita bayangkan.
2. Rumus Excel Untuk Perhitungan Lembur
Selanjutnya mari kita bahas rumus – rumus apa saja yang akan kita gunakan untuk menghitung lembur dalam Excel.
Ada dua rumus utama yang akan kita gunakan untuk menghitung lembur karyawan berdasarkan absensi dalam Excel.
Pertama kita akan menggunakan rumus IF dan rumus ini akan kita gunakan untuk memisahkan kondisi sesuai dengan dasar perhitungan lembur yang sudah dijelaskan diatas.
Selanjutnya rumus yang kedua kita akan menggunakan rumus AND dan rumus ini digunakan karena pengujian logika yang kita pisahkan tidak hanya terdiri dari satu saja untuk satu hasil nilai TRUE dalam rumus IF tersebut.
Dalam prosesnya nanti kedua rumus ini yaitu rumus IF dan rumus AND akan kita gabungkan menjadi satu.
Baca Juga : Menggabungkan Rumus IF dan AND Untuk Menguji 2 Logika Dengan 1 Hasil Dalam Excel
Jika belum memahami cara penggunaan kedua rumus tersebut silahkan buka dan pelajari dalam kategori Fungsi Logika.
Selain kedua rumus tersebut kita juga akan menambahkan rumus lainnya seperti rumus TEXT atau rumus TIME meskipun kedua rumus ini tidak terlalu berpengaruh kedalam perhitungan jam lembur.
3. Contoh Menghitung Lembur Dalam Excel Berdasarkan Absensi
Setelah membahas tentang rumus apa saja yang akan digunakan serta dasar perhitungan lembur mari kita melihat contoh perhitungannya.
Seperti yang disebutkan diatas bahwa dasar perhitungan lembur adalah selisih jam masuk dan jam pulang.
Dokumen yang biasa digunakan untuk menghitung lembur serta memuat jam masuk dan jam pulang adalah Absensi Karyawan.
Absensi Karyawan inilah yang akan kita jadikan dasar menghitung jam lembur dalam Excel.
Untuk contoh perhitungan lembur dalam Excel silahkan perhatikan gambar berikut ini :
Dalam conth tersebut sudah ada Tabel Perhitungan Lembur berdasarkan absensi mulai dari tanggal 1 sampai dengan akhir bulan.
Untuk rumus Excel menghitung lembur setiap kolomnya akan kita bahas satu persatu.
=> Rumus Menampilkan Hari Untuk Perhitungan Lembur
Rumus Excel yang pertama untuk menghitung lembur pada contoh diatas adalah Nama Hari atau kolom J.
Menampilkan nama hari ini akan menjadi hal yang sangat penting karena memang nama hari tersebut akan dijadikan sebagai salah satu kriteria pada rumus lembur.
Saya sudah cukup banyak membahas tentang cara menampilkan nama hari dalam Excel dan jika ingin mempelajarinya silahkan buka dalam kategori Tanggal dan Waktu.
Baca Juga : Cara Menampilkan Hari Dalam Excel Menggunakan Rumus TEXT dan Format Cells
Pada perhitungan lembur diatas untuk menampilkan nama hari kita menggunakan rumus TEXT Excel.
Adapun rumus Excel yang digunakan untuk menampilkan nama hari dalam perhitungan lembur Cell J4 adalah sebagai berikut :
=TEXT(I4;”dddd”)
Mohon diperhatikan, nama hari akan dijadikan sebagai kriteria dan kriteria tersebut dalam bentuk bahasa Indonesia.
Jadi, pastikan nama hari yang tampil dalam kolom tersebut adalah bahasa Indonesia bukan bahasa lainnya.
Untuk menampilkan nama hari dalam bahasa Indonesia bisa dengan mengubah Regional Setting atau bisa juga dengan cara menggabungkan kode LCID dengan rumus TEXT Excel.
=> Menampilkan Pilihan Libur Nasional Untuk Perhitungan Lembur
Selanjutnya mari kita buat pilihan Libur Nasional atau bukan pada kolom K atau kolom Libur Nasional.
Dalam kolom ini akan dibuatkan Dropdown List atau daftar pilih dengan teks yang akan muncul adalah “Ya” dan “Tidak”.
Untuk cara membuat pilihan pada Dropdown List tersebut langkah – langkahnya adalah sebagai berikut :
- Blok Cell K4 sampai dengan K35
- Klik Tab Data
- Klik Data Validation pada kelompok menu Data Tools
- Klik Data Validation dan akan muncul kotak dialog Data Validation
- Dalam kotak dialog Data Validation klik Tab Setting
- Pada kotak Allow pilih : List
- Pada kotak Source ketik : Ya; Tidak
- Klik OK
Sampai dengan langkah yang kedelapan diatas kita sudah selesai membuat pilhan “Ya” dan “Tidak”.
Baca Juga : Cara Membuat Dropdown List di Excel
Pada kotak ini ada dua pembagian dan dua kondisi yang digunakan pada perhitungan lembur.
Untuk hari Minggu pilihan “Ya” atau “Tidak” ini tidak akan berpengaruh kedalam hitungan lembur karena secara default hari Minggu dianggap hari Libur.
Sedangkan untuk hari Senin sampai dengan Sabtu jika dilipih “Ya” maka akan dianggap sebagai hari Libur.
Sebaliknya jika dipilih “Tidak” atau dikosongkan saja maka akan dianggap sebagai hari biasa atau bukan hari Libur.
=> Rumus Menghitung Total Jam Kerja Untuk Perhitungan Lembur
Selanjutnya mari kita hitung kolom N atau kolom Total Jam Kerja yang merupakan selisih dari Jam masuk dan jam pulang.
Adapun rumus yang digunakan untuk menghitung Total Jam lembur pada Cell N4 adalah sebagai berikut :
=IF(L4<>””;(M4–L4)*24;0)
Dengan rumus tersebut maka selisih jam bekerja akan otomatis muncul tetapi ini hanya jam kerjanya saja belum dihitung jam lembur.
=> Rumus Menghitung Jam Lembur Untuk Perhitungan Lembur
Selanjutnya kita akan menghitung Jam Lembur pada kolom O atau kolom Jam Lembur.
Kolom ini akan menampilkan jam lembur jika memang ada atau muncul kelebihan jam pada kolom N.
Adapun rumus yang digunakan pada Cell O4 untuk menghitung Jam Lembur adalah sebagai berikut :
=IF(AND(J4=”Sabtu”;L4<>””;M4<=TIME(13;0;0);K4<>”Ya”);0;IF(AND(J4=”Sabtu”;L4<>””;M4>TIME(13;0;0);K4<>”Ya”);N4-1-4;IF(AND(J4=”Sabtu”;L4<>””;K4<>”Ya”);N4-1;IF(AND(J4<>”Minggu”;M4>TIME(12;0;0);K4=”Ya”);N4-1;IF(AND(J4<>”Minggu”;M4<=TIME(12;0;0);K4=”Ya”);N4;IF(AND(J4<>”Minggu”;N4>8;K4<>”Ya”);N4-1-7;IF(AND(J4=”Minggu”;L4<>””;M4<=TIME(12;0;0));N4;IF(AND(J4=”Minggu”;L4<>””;M4>TIME(12;0;0));N4-1;IF(AND(J4=”Minggu”;L4<>””);N4-1;0)))))))))
Rumus diatas akan memisahkan beberapa kondisi yang akan muncul pada perhitungan jam lembur.
Kondisi tersebut diantaranya adalah sebagai berikut :
- Hari Senin sampai Sabtu dan statusnya libur nasional dengan jam bekerja 1 hari ( lebih dari jam 12:00:00 ), Total Jam Kerja akan dikurangi dengan 1 jam istirahat
- Hari Senin sampai dengan Sabtu dan statusnya libur nasional dengan jam bekerja hanya setengah hari ( dibawah atau sama dengan jam 12:00:00 ), Total Jam Kerja tidak akan dikurangi dengan istirahat
- Hari Senin sampai dengan Sabtu tapi statusnya bukan libur nasional serta Total Jam Kerja lebih dari 9 Jam, maka Total Jam Kerja akan dikurang dengan 8 jam normal dan 1 jam istirahat
- Kondisi yang terakhir adalah untuk hari Minggu dan Total Jam akan dikurangi dengan 1 Jam istirahat
Jika ada yang kurang sesuai dengan pola ini terutama untuk Jam Istirahat silahkan ubah pada bagian rumus ini.
=> Rumus Menghitung Kelipatan 1.5 Untuk Perhitungan Lembur
Selanjutnya mari kita hitung kelipatan lembur yang pertama yaitu untuk perhitungan 1.5 dikali jam lembur.
Pada bagian ini yang akan muncul hanya untuk lembur pada hari biasa saja seperti yang disebutkan diatas.
Adapun rumus perhitungan lembur dengan kelipatan 1.5 pada Cell P4 adalah sebagai berikut :
=IF(AND(J4<>”Minggu”;O4>1;K4<>”Ya”;L4<>””);1;IF(AND(J4<>”Minggu”;O4<=1;K4<>”Ya”;L4<>””);O4;0))
Dengan rumus diatas maka jika lembur pada hari biasa lebih dari 1 jam yang akan muncul adalah 1.
Sedangkan jika jam lembur yang muncul kurang dari 1 maka jam tersebutlah yang akan muncul.
=> Rumus Menghitung kelipatan 2 Untuk Perhitungan Lembur
Selanjutnya kita akan menghitung kelipatan 2 untuk jam lembur yaitu kolom Q atau kolom 2.
Pada kolom ini yang akan muncul adalah jam lembur untuk hari libur dan juga hari biasa.
Adapun rumus yang digunakan untuk menghitung kelipatan lembur 2 pada Cell Q4 adalah sebagai berikut :
=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–P4;IF(AND(J4=”Jumat”;O4>5;L4<>””);5;0)))))))
Rumus tersebut akan menampilkan angka 7 jika lembur pada hari libur dan akan menampilkan sisa jam setelah dikurang dengan kolom P jika libur pada hari biasa.
=> Rumus Menghitung kelipatan 3 Untuk Perhitungan Lembur
Selanjutnya kita akan menampilkan jam lembur dengan perkalian kelipatan 3 atau kolom R.
Pada kolom ini yang akan muncul hanya lembur untuk hari libur saja sesuai dengan dasar perhitungan lembur yang sudah dijelaskan diatas.
Adapun rumus menghitung lembur kelipatan 3 pada Cell R4 adalah sebagai berikut :
=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 kolom ini jika jam lembur pada hari libur lebih dari atau sama dengan 8 jam maka akan muncul angka 1.
Sedangkan jika jam lembur lebih dari 7 tapi kurang dari 8 maka yang muncul adalah angka selisih jam lembur tersebut.
=> Rumus Menghitung kelipatan 4 Untuk Perhitungan Lembur
Selanjutnya kita akan menghitung jam lembur dengan kelipatan 4 pada kolom S.
Pada kolom ini yang akan muncul adalah sisa jam lembur setelah ditampilkan pada kolom Q dan kolom R.
Adapun rumus yang digunakan untuk menghitung lembur dengan kelipatan 4 pada Cell S4 adalah sebagai berikut :
=IF(AND(J4<>”Minggu”;J4<>”Jumat”;K4=”Ya”;L4<>””;O4>8);O4–Q4–R4;IF(AND(J4=”Minggu”;L4<>””;O4>8);O4–Q4–R4;IF(AND(J4=”Jumat”;K4=”Ya”;L4<>””;O4>6);O4–Q4–R4;0)))
Sesuai dengan penjelasan diatas bahwa pada kolom ini yang akan muncul hanya jam lembur pada hari libur saja sehingga rumusnya relatif jauh lebih pendek jika dibandingkan dengan rumus diatasnya.
=> Rumus Menghitung Total Jam Lembur
Selanjutnya rumus yang terakhir kita akan mengalikan jam lembur yang sudah dipisahkan diatas dengan kelipatannya.
Rumus pada kolom ini cukup mudah karena hanya akan mengalikan jam lembur dengan kelipatannya saja.
Adapun rumus yang digunakan pada Cell T4 adalah sebagai berikut :
=(P4*$P$3)+(Q4*$Q$3)+(R4*$R$3)+(S4*$S$3)
Rumus tersebut akan menghitung Jumlah jam lembur setelah dikalikan dengan masing – masing kelipatannya.
Setelah kita selesai menghitung jumlah jam lembur per hari selanjutnya silahkan kalikan dengan besaran lembur perhari untuk mendapatkan jumlah lembur dalam bentuk rupiahnya.
Untuk rumus lainnya dibagian bawah kita cukup menggunakan penjumlahan dengan rumus SUM saja.
Silahkan pelajari dan pahami rumus – rumus yang sudah dijelaskan diatas supaya mudah mengganti atau mengubahnya jika dirasa ada yang kurang sesuai dengan peraturan lembur ditempat kita bekerja.
4. Contoh File Excel Menghitung Lembur
Untuk kelengkapan pembelajaran tentang menghitung lembur saya akan lampirkan contoh filenya dalam bentuk Excel.
Ada dua contoh file yang bisa diambil dan supaya mudah saya akan beri nama dengan versi 1 dan versi 2.
=> Contoh File Lembur versi 1.0
Rincian dan penjelasan untuk lembur yang pertama ini adalah sebagai berikut :
- Jumlah Hari Kerja : 6 Hari, Senin sampai Sabtu
- Hari Jum’at dihitung sebagai lembur hari pendek
- Jam kerja mulai dari 08:00 sampai dengan 16:00
- Wajib setting nama hari kedalam Bahasa Indonesia
- Hari Sabtu setengah hari( 08:00 s.d 13:00 )
=> Contoh File Lembur versi 2.0
Rincian dan penjelasan untuk lembur yang kedua ini adalah sebagai berikut :
- Jumlah Hari Kerja : 5 Hari, Senin sampai Jum’at
- Hari Jum’at dihitung sebagai lembur hari pendek
- Jam kerja mulai dari 08:00 sampai dengan 17:00
- Wajib setting nama hari kedalam Bahasa Indonesia
- Hari Sabtu dihitung sebagai hari libur
Silahkan pilih satu diantara dua contoh file lembur Excel sesuai dengan penjelasan diatas.
Itulah pembahasan kita kali ini tentang cara menghitung lembur berdasarkan absensi dalam Excel dan semoga bermanfaat untuk semua pembaca.
