Lembur Karyawan, Rumus Untuk Menghitung Lembur / Overtime Dalam Microsoft Excel
Untuk menghitung overtime secara manual memang bisa dilakukan, tetapi jika jumlah karyawan sangat banyak maka ini akan sangat menyita waktu.
Microsoft Excel menyediakan beberapa rumus yang dapat membantu untuk menghitung lembur atau overtime tersebut.
Tentu rumus ini akan disesuaikan dengan ketetapan lembur yang ada, baik ketetapan dari pemerintah ataupun kesepakatan bersama antara perusahaan dengan karyawannya.
Penghitungan lembur ini telah dibahas dalam artikel sebelumnya, tetapi khusus untuk pembahasan atau penjelasan tentang rumus yang digunakan akan saya jelaskan dalam artikel ini.
Sebelum melanjutkan sebaiknya pelajari terlebih dahulu pembahasan tentang overtime atau lembur tersebut karena isi dari artikel ini merupakan bagian dari artikel tersebut.
Seperti yang saya sebutkan dalam artikel yang membahas lembur bahwa untuk penjelasan rumus akan dibuatkan artikel tersendiri supaya lebih jelas dan artikel tersebut tidak terlalu panjang.
Dalam artikel ini akan dibahas dan dijelaskan tentang rumus yang digunakan juga akan mencocokan rumus tersebut dengan ketetapan lembur yang dibuat oleh pemerintah.
Pencocokan rumus dan ketetapan ini supaya kita bisa bersama - sama menyusun sebuah rumus yang benar - benar sesuai dengan peraturan tersebut.
Jika Anda telah membaca artikel sebelumnya tentang menghitung lembur berdasarkan absensi maka form untuk pengisian jam kerja tersebut saya akan bagi menjadi dua bagian pembahasan, yaitu rumus penghitungan gaji dan rumus menghitung jam lembur.
Jika digambarkan maka dua bahasan rumus tersebut akan seperti pada gambar dibawah ini :
Gambar sebelah kiri akan kita bahas terlebih dahulu dan berikutnya akan kita bahas rumus yang kedua.
Ada beberapa kolom yang menggunakan rumus dalam bagian ini, tapi ini tidak perlu diinput secara manual karena data yang ditampilkan diambil dari sheet DATA KRY.
- Rumus Identitas Karyawan dan Data Gaji
Kolom atau cell pada bagian ini yaitu C3 (Nama), C4 (Jabatan), C5 (Status), G7 ( Gaji Pokok), C8 (total jam lembur), E8 (tarif lembur perjam), G8 (total rupiah lembur) dan G9 (Tunjangan).
Mayoritas pada cell tersebut menggunakan rumus VLOOKUP untuk pengambilan data dari sheet DATA KRY.
Untuk mempermudah pemahaman silahkan pelajari terlebih dahulu tentang fungsi rumus VLOOKUP.
Rumus yang digunakan untuk cell - cell tersebut diatas adalah sebagai berikut :
Rumus Excel Untuk Menghitung Lembur / Overtime
Jika Anda telah membaca artikel sebelumnya tentang menghitung lembur berdasarkan absensi maka form untuk pengisian jam kerja tersebut saya akan bagi menjadi dua bagian pembahasan, yaitu rumus penghitungan gaji dan rumus menghitung jam lembur.
Jika digambarkan maka dua bahasan rumus tersebut akan seperti pada gambar dibawah ini :
Gambar sebelah kiri akan kita bahas terlebih dahulu dan berikutnya akan kita bahas rumus yang kedua.
1. Rumus Menghitung Gaji Excel
Ada beberapa kolom yang menggunakan rumus dalam bagian ini, tapi ini tidak perlu diinput secara manual karena data yang ditampilkan diambil dari sheet DATA KRY.
- Rumus Identitas Karyawan dan Data Gaji
Kolom atau cell pada bagian ini yaitu C3 (Nama), C4 (Jabatan), C5 (Status), G7 ( Gaji Pokok), C8 (total jam lembur), E8 (tarif lembur perjam), G8 (total rupiah lembur) dan G9 (Tunjangan).
Mayoritas pada cell tersebut menggunakan rumus VLOOKUP untuk pengambilan data dari sheet DATA KRY.
Untuk mempermudah pemahaman silahkan pelajari terlebih dahulu tentang fungsi rumus VLOOKUP.
Rumus yang digunakan untuk cell - cell tersebut diatas adalah sebagai berikut :
Cell : C3 / Nama
Rumus : =VLOOKUP(C2;'DATA KRY'!$B$13:$J$23;2;0)
Penjelasan : Mengambil data nama karyawan dari sheet DATA KRY.
Untuk cell lainnya selain Lembur maka gunakan rumus tersebut diatas hanya saja kolom pengambilan tabelnya yang dirubah dari 2 menjadi 3, 4, 5 dan 6.
Cell : C8
Rumus : =T36
Penjelasan : Mengambil total jam lembur dari cell atau range T36
Cell : E8
Rumus : =G7/173
Penjelasan : Membagi gaji pokok dengan 173 untuk menghitung tarif lembur perjam.
- Rumus Potongan Gaji
Untuk potongan gaji rumus yang digunakan tidak terlalu sulit, hanya mengalikan gaji pokok dengan tarif - tarif yang berlaku.
Untuk pembulatan angka saya menggunakan bantuan dari fungsi ROUD, silahkan pelajari dalam artikel dibawah ini :
Selain itu untuk potongan - potongan tersebut telah saya bahas juga dalam artikel sebelumnya, jadi tidak akan sulit untuk menghitungnya.
- Menghitung PPH 21
Untuk menghitung PPH 21 ini ada dalam cell C25 dan seterusnya, saya gunakan warna berbeda karena ini sebenarnya diluar dari perhitungan gaji.
Penghitungan PPH 21 telah saya bahas dalam artikel yang lain, silahkan untuk dipelajari.
Penting untuk diketahui bahwa penghitungan ini bersifat otomatis sehingga dengan berubahnya total gaji maka PPH 21 inipun akan ikut berubah menyesuaikan dengan perubahan gaji tersebut.
Untuk rumus lainnya yang tidak dibahas pada bagian ini silahkan langsung dilihat pada contoh filenya yang akan saya sertakan dalam artikel menghitung gaji karyawan.
2. Rumus Menghitung Jam Lembur/Overtime Dalam Excel
Pembahasan rumus yang kedua ini menurut saya jauh lebih penting karena ini merupakan inti dari pembahasan artikel kita kali ini.
Dalam aturan lembur yang telah kita bahas sebelumnya terdapat banyak kondisi yang harus disesuikan untuk menghitung lembur, misalnya hari kerja biasa dan hai libur maka penetapan lemburnyapun berbeda.
Untuk mengatasi ini saya menggunakan fungsi IF yang digabungkan dengan Fungsi AND sebagai pemisah dari masing - masing kriteria tersebut.
Masalah yang mungkin muncul untuk kita adalah bagaimana jika lembur ini ingin dihitung berderet sesuai dengan nama karyawan masing - masing tetapi ada dalam satu sheet yang sama ?
Untuk kondisi tersebut sudah saya jelaskan dalam artikel yang lain dan silahkan untuk dipelajari.
Rumus yang saya buat adalah sebagai berikut :
- Rumus Cell I4 atau kolom Tanggal
Kolom Tanggal ini saya buat otomatis, cara mengisinya adalah dengan memasukan tanggal pertama ( tanggal satu ) pada cell I4 dan otomatis cell I5 sampai dengan I34 akan menyesuaikan.
Rumus yang saya gunakan sangat mudah, hanya menambahkan satu dari tanggal pada cell yang diatasnya, atau pada cell I5 saya gunakan rumus sebagai berikut :
=I4+1
Untuk cell I6 sampai dengan I34 silahkan copy paste rumus tersebut diatas.
- Rumus Cell J4 atau kolom Hari
Kolom ini merupakan teks dari tanggal yang saya munculkan menjadi nama hari, fungsinya adalah untuk membedakan lembur pada hari biasa dan hari libur (Minggu).
Rumus yang saya gunakan adalah sebagai berikut :
=TEXT(I4;"DDDD")
Ini yang sangat penting bahwa saya menggunakan format hari ini dalam bahasa Indonesia sehingga jika komputer menggunakan regional setting selain Indonesia rumus - rumus ini tidak akan berfungsi.
- Rumus Kolom K4 atau Libur Nasional
Kolom ini untuk menentukan apakah hari kerja biasa tersebut jatuh sebagai hari libur nasional atau tidak.
Jika libur nasional tentu perhitungan lemburnya berbeda dengan hari biasa, sehingga perlu untuk dipisahkan.
Dalam kolom ini saya isi dengan dropdown list atau combobox dengan dua pilihan yaitu "Ya" dan "Tidak".
Untuk cara membuatnya silahkan pelajari dalam artikel dibawah ini :
Jika ada hari kerja biasa (senin - sabtu) yang kebetulan karyawan masuk kerja dan dihitung lembur maka dalam kolom tersebut harus dipilih "Ya".
- Rumus Kolom L dan M (Jam Kerja)
Kolom ini diisi manual dan format yang diberikan dalah Time. Isi dengan jam masuk dan jam keluar bekerja dari karyawan yang bersangkutan.
- Rumus Kolom N4 (Total Jam Kerja)
Rumus ini akan menghitung total jam yang dumasukan dalam kolom L dan M, sebelum dikurangi jam istirahat.
Rumus yang saya gunakan adalah sebagai berikut :
=IF(L4<>"";(M4-L4)*24;0)
Kolom ini saya menggunakan format number bukan time sehingga total jam tersebut saya kalikan 24.
Untuk cell N5 sampai dengan N34 silahkan copy paste rumus tersebut.
- Rumus Kolom O4 (Jam Lembur)
Rumus jam lembur menggunakan beberapa kriteria untuk pemenuhan kondisi hasilnya.
Dalam kolom inipun saya menggunakan gabungan fungsi IF dan AND sebagai penambah jumlah kriteria syarat atau kondisinya.
Untuk kolom ini saya menggunakan rumus sebagai berikut :
=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>9;K4<>"Ya");N4-1-8;IF(AND(J4="Minggu";L4<>"");N4-1;0))))
Dalam rumus ini ada 4 kondisi yang diperhitungkan yaitu kolom J4 atau Hari dengan syarat hari minggu atau bukan hari minggu, K4 atau kolom Libur Nasional dengan syarat "Ya" atau "Tidak" dan kolom M4 atau Jam kerja dengan syarat lebih dari jam 12:00 atau Kurang dari jam 12:00
Artinya jika karyawan lembur pada hari libur nasional dan bekerja lebih dari jam 12:00 maka jam tersebut akan dikurangi dengan 1 jam istirahat, sedangkan jika kurang dari jam 12:00 maka tidak akan dikurangi dengan 1 jam istirahat.
- Rumus Kolom P4 atau Rate Lembur 1,5x
Rumus ini akan menghitung semua kondisi dari mulai hari sampai dengan libur nasional yang memenuhi syarat sesuai dengan aturan lembur.
Rumusnya saya buat 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 ini jika memnuhi syarat untuk jam lembur yang dikalikan rate 1,5 maka otomatis akan muncul dalam cell tersebut.
- Rumus Kolom Q4 atau Rate Lembur 2x
Rumus ini akan memisahkan hitungan jam lembur dengan syarat atau kondisi yang masuk kedalam rate 2x.
Rumusnya 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;IF(AND(J4="Jumat";O4>5;L4<>"");5;0)))))))
Inti dari rumus ini adalah jika lembur pada hari kerja biasa maka jumlah jam adalah jumlah sisa jam setelah dikurangikolom P4, sedangkan jika lembur pada hari libur nasional maka akan memunculkan 7 jam pertam.
- Rumus R4 atau Rate Lembur 3x
Dalam kolom ini yang muncul hanya lembur untuk hari libur atau hari libur nasional saja.
Rumusnya 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))))))
Dengan rumus ini jika karyawan lembur lebih dari 8 jam maka akan muncul 1 jam atau jam ke-8.
- Rumus Kolom S4 atau Rate lembur 4x
Kolom ini akan menghitung jam lembur karyawan pada hari libur atau hari libur nasional yang memiliki lebih dari 9 jam.
Rumusnya 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)))
Jadi, dalam kolom ini akan muncul sisa dari jam lembur pada hari libur dan libur nasional setelah dikurangi kolom P4 dan Q4.
- Rumus Kolom T4 atau Total Jam Lembur
Kolom ini akan menghitung seluruh jam lembur setelah dikalikan dengan rate sesuai dengan aturan lembur.
Rumusnya adalah sebagai berikut :
=(P4*$P$3)+(Q4*$Q$3)+(R4*$R$3)+(S4*$S$3)
Jadi dengan rumus ini maka dalam masing - masing cell kolom ini akan muncul total jam lembur dan selanjutnya pada cell T36 akan dihitung seluruh jam lembur pada bulan tersebut dengan fungsi SUM.
Itulah penjelasan singkat dari masing - masing rumus yang ada dalam kolom penghitungan lembur.
Untuk detailnya silahkan lihat pada contoh filenya yang telah saya sertakan dalam artikel berikut ini:
Baca : Menghitung Gaji, Cara Menghitung Gaji Dengan Rumus Dalam Microsoft Excel
Semoga artikel ini bermanfaat untuk semua pembaca.
Gk Ngeh aing mah😭😥
ReplyDelete