Friday, May 4, 2018

Rumus IF Untuk Menghitung Jam Lembur


Sering Bekerja Lembur Hampir Setiap hari ? yuk kita otak-atik rumus excel untuk menghitung jam lembur dan upahnya. Dengan sedikit logika dan penggunaan rumus IF beberapa kondisi, dibantu fungsi MAX dan MIN, kita bisa menghitung berapa sih jam lembur yang harus dibayar, yang merupakan hak pekerja atas jerih payah dan keringatnya yang tidak mengenal lelah untuk perusahaan tercinta.


rumus excel overtime
Contoh Soal:


Si Badu Bekerja sebagai karyawan di sebuah perusahaan yang mempekerjakan karyawannya 6 hari dalam seminggu. Ia merupakan karyawan tetap dan dibayar secara bulanan dengan total upah tetap (pokok + tunjangan) senilai Rp. 3.460.000. Dikarenakan perusahaan tempat ia bekerja sangat dinamis, menuntut karyawannya untuk siap bekerja kapan saja tanpa mengenal waktu, Si Badu pun bekerja selalu bekerja melebihi jam dinas. Bahkan pada hari libur resmi dan hari minggu pun, terkadang si Badu harus masuk kantor untuk menyelesaikan tugas dengan loyalitas tiada banding.

Sebagai kompensasi atas kerja kerasnya, Si Badu menerima upah kerja over time yang entah kenapa ia merasa nilainya tidak sebanding dengan jerih payah dan waktu yang ia korbankan. Merasa ada yang tidak beres, maka si Badu meminta tolong anda untuk menghitungkan upah lembur dia yang seharusnya sesuai ketentuan pemerintah, sebagai data pendukung untuk komplain ke atasannya.

Berikut data jam masuk dan jam pulang Si Badu dalam waktu Seminggu.

soal excel hitung lembur

Sebelum membahas rumus excel untuk menghitung jam dan upah lembur, alangkah baiknya terlebih dahulu kita mengetahui ketentuan perhitungan upah lembur yang berlaku. Perhitungan lembur dalam pembahasan contoh rumus excel kali ini berdasarkan pada  KEPMEN NO. 102 TH 2004.
KEPMEN NO. 102 TH 2004 tentang upah lembur

Untuk pekerja yang upahnya dibayar secara bulanan, maka nilai upah sejam = upah sebulan dibagi dengan 173. Si Badu bekerja dengan mendapatkan total upah tetap bulanan senilai Rp. 3.460.000, sehingga upah sejam = Rp 3.460.000/173 = Rp. 20.000/jam.

KEPMEN NO. 102 TH 2004 tentang ketentuan lembur


Si Badu bekerja di perusahaan yang memperkerjakan karyawannya 6 hari dalam seminggu, sehingga kita gunakan perhitungan sesuai pasal 11 point a. dan b. 

Berdasarkan ketentuan tersebut, ada 3 skenario perhitungan lembur yang berbeda yaitu.
  1. Lembur pada hari kerja
  2. Lembur pada hari libur istirahat mingguan atau pada hari libur resmi yang jatuh pada hari biasa
  3. Lembur pada hari libur resmi yang jatuh pada hari kerja terpendek. Anggaplah hari Sabtu sebagai hari kerja terpendek.

Ketiga kondisi tersebut dapat digabungkan menggunakan rumus IF. Namun karena rumus cukup panjang, alangkah baiknya kita bahas terlebih dahulu masing-masing kondisi supaya memudahkan pemahaman.

Menghitung Jam Lembur Karyawan Pada Hari Kerja.


Dengan mengacu ke ketentuan pemerintah, upah lembur pada hari kerja dihitung sebagai berikut:
  • Jam kerja lembur satu jam pertama harus dibayar sebesar 1,5 kali upah sejam
  • Jam kerja lembur selanjutnya harus dibayar sebesar 2 kali upah sejam

Ketentuan untuk menghitung jam lembur dibayar pada hari kerja tersebut dapat dituliskan dalam bentuk formula excel sebagai berikut:

=MIN(1,jamLembur)*1.5+MAX(0,jamLembur-1)*2

  • Bagian rumus MIN(1,jamLembur)*1.5 digunakan untuk menghitung jumlah jam lembur di bayar untuk kerja lembur pada jam pertama.
    • Jika lembur lebih dari 1 jam  ⇒ Jam lembur dibayar pada 1 jam pertama = 1 x 1,5 = 1,5 jam.
    • Jika lembur dari 1 jam ⇒ Jam Lembur dibayar pada 1 jam pertama = jamLembur x 1,5 jam.

  • Bagian Rumus MAX(0,jamLembur-1)*2 digunakan untuk menghitung jam lembur di bayar untuk kerja lembur pada jam ke-2 dan seterusnya
    • Jika lembur kurang dari 1 jam ⇒  jam lembur dibayar pada jam ke-2 dan seterusnya = 0
    • Jika lembur lebih dari 1 jam ⇒  jam lembur dibayar pada jam ke-2 dan seterusnya = (jamLembur-1) x 2 jam.

Untuk lebih jelasnya, perhatikan gambar berikut.

Contoh rumus excel hitung lembur pada hari kerja


Penjelasan rumus:

Rumus nomor 1: =IF(D2="",E2-C2,E2-D2)
Digunakan untuk menghitung selisih antara jam pulang lembur dengan jam pulang normal  pada hari kerja.  Fungsi IF digunakan untuk mengecek sel D2 (jam pulang normal) apakah memiliki konten atau sebaliknya (blank). Jika blank maka akan dianggap sebagai hari libur, dan jam kerja lembur dihitung sebagai selisih jam pulang dengan jam masuk. Hasil rumus ini sebenarnya berupa bilangan desimal yang diformat hh:mm untuk menampilkan jam dan menit.

Rumus nomor 2:  =F2*24
Formula tersebut digunakan untuk mengkonversi nilai waktu (hasilrumus nomor 1) menjadi bilangan jam. Caranya dengan mengalikan nilai waktu dengan 24.

Rumus nomor 3:  =MIN(1,G2)*1.5+MAX(0,G2-1)*2
Formula ini digunakan untuk menghitung faktor pengali atau jumlah jam lembur dibayar (hasil nomor rumus nomor 2) pada hari kerja dengan mengacu pada KEPMEN NO. 102 TH 2004. Detail cara kerja rumus sudah dijelaskan di atas.

Rumus nomor 4:  =H2*20000
Rumus yang terakhir ini digunakan untuk mendapatkan nilai upah lembur dalam sehari pada hari kerja. Karena upah sejam si Badu adalah Rp 20.000, maka upah yang harus dibayar = jumlah jam lembur dibayar x Rp 20.000

Menghitung Jam Lembur Karyawan Pada Hari Minggu dan Libur Resmi Yang Jatuh pada hari biasa.


Sesuai ketentuan : 

Apabila lembur kerja dilakukan pada hari istirahat mingguan dan / atau hari libur resmi untuk waktu hari kerja 40 (empat puluh) jam seminggu maka:
  • Perhitungan upah kerja lembur pada 7 (tujuh) jam pertama dibayar 2 (dua) kali upah sejam, dan jam kedelapan dibayar 3 (tiga) kali upah sejam dan jam lembur kesembilan dan kesepuluh dibayar 4 (empat) kali upah sejam.


Ketentuan di atas dapat diterjemahkan ke dalam rumus excel sebagai berikut:

=MIN(7,jamLembur)*2+MIN(MAX(0,jamLembur-7),1)*3+MAX(0,jamLembur-8)*4

  • Bagian rumus MIN(7,jamLembur)*2 digunakan untuk menghitung jam lembur dibayar pada 7 jam pertama.
    • Jika lembur kurang dari 7 jam ⇒ jam lembur dibayar = jamLembur x 2 jam.
    • Jika lembur lebih dari 7 jam ⇒ jam lembur dibayar = 7 x 2 jam
  • Bagian rumus MIN(MAX(0,jamLembur-7),1)*3 digunakan untuk menghitung jam lembur dibayar pada jam ke-8.
    • Jika lembur kurang dari 7 jam ⇒ jam lembur dibayar pada jam ke-8 = 0
    • Jika lembur lebih dari 7 jam ⇒ jam lembur dibayar pada jam ke-8 = maksimum 1 x 3 jam.
  • Bagian rumus MAX(0,jamLembur-8)*4 digunakan untuk menghitung jumlah jam lembur dibayar pada jam ke-9 dan seterusnya.
    • Jika lembur kurang dari 8 jam ⇒ jam lembur dibayar pada jam ke-9 dan seterusnya = 0
    • Jika lembur lebih dari 8 jam ⇒ jam lembur dibayar pada jam ke-9 dan seterusnya = (jamLembur – 8) x 4 jam.

Penerapannya dalam excel akan seperti ini.

Contoh rumus excel  hitung lembur pada hari libur
 .


Menghitung Jam Lembur Karyawan Pada Hari Libur Resmi Yang Jatuh Pada Hari Kerja Terpendek.




Sesuai ketentuan pemerintah: 
Apabila hari libur resmi jatuh pada hari kerja terpendek, perhitungan upah lembur 5 (jam) pertama dibayar 2 (dua) kali upah sejam, jam keenam 3 (tiga) kali upah sejam dan jam lembur ketujuh dan kedelapan 4 (empat) kali upah sejam,

Ketentuan pehitungan lembur pada hari libur yang jatuh pada hari kerja terpendek tersebut dapat dirumuskan sebagai berikut:

=MIN(5,jamLembur)*2+MIN(MAX(0,jamLembur-5),1)*3+MAX(0,jamLembur-6)*4

  • Bagian rumus MIN(5,jamLembur)*2 digunakan untuk menghitung jam lembur dibayar pada 5 jam pertama.
    • Jika lembur kurang dari 5 jam ⇒ jam lembur dibayar pada 5 jam pertama = jamLembur x 2 jam.
    • Jika lembur lebih dari 5 jam ⇒ jam lembur dibayar pada 5 jam pertama = 5 x 2 jam
  • Bagian rumus MIN(MAX(0,jamLembur-5),1)*3 digunakan untuk menghitung jam lembur dibayar pada jam ke-6.
    • Jika lembur kurang dari 5 jam ⇒ jam lembur dibayar pada jam ke-6 = 0
    • Jika lembur lebih dari 5 jam ⇒ jam lembur dibayar pada jam ke-6 = maksimum 1 x 3 jam.
  • Bagian rumus MAX(0,jamLembur-6)*4 digunakan untuk menghitung jumlah jam lembur dibayar pada jam ke-7, ke-8 dan seterusnya.
    • Jika lembur kurang dari 6 jam ⇒ jam lembur dibayar pada jam ke-7 dan seterusnya = 0
    • Jika lembur lebih dari 6 jam ⇒ jam lembur dibayar pada jam ke-7 dan seterusnya = (jamLembur – 6) x 4 jam.
Penerapannya dalam excel akan seperti ini.

Contoh Rumus Excel Hitung Lembur pada hari Libur Resmi Pendek


Mengkombinasikan rumus perhitungan jam lembur pada hari kerja dan hari libur.


Seperti sudah dipaparkan di bagian awal, type perhitungan faktor pengali  jumlah jam lembur dibayar dipengaruhi oleh 3 kondisi yaitu:

  • Kondisi 1 = Hari Kerja
  • Kondisi 2 = Hari libur istirahat mingguan atah hari libur resmi yang jatuh pada hari kerja biasa
  • Kondisi 3 = Hari libur resmi yang jatuh pada hari kerja terpendek.

Masing-masing rumus untuk masing-masing kondisi sudah kita bahas di atas, dan penerapannya di excel dengan menggunakan contoh tabel jam masuk dan pulan kerja si Badu, kita dapat meringkas rumus excel penghitung jam lembur dibayar si badu pada sel H2 sebagai berikut:

  • Kondisi 1             B2=1
  • Kondisi 2             B2=2
  • Kondisi 3             B2=3
  • Rumus Kondisi 1  =MIN(1,G2)*1.5+MAX(0,G2-1)*2
  • Rumus Kondisi 2  =MIN(7,G2)*2+MIN(MAX(0,G2-7),1)*3+MAX(0,G2-8)*4
  • Rumus Kondisi 3  =MIN(5,G2)*2+MIN(MAX(0,G2-5),1)*3+MAX(0,G2-6)*4

Kemudian kita bisa mengkombinasikan ketiga rumus tersebut menggunakan bantuan fungsi IF. Secara umum rumusnya dapat dituliskan sebagai berikut:

=IF(kondisi1,rumus_kondisi1,IF(konsisi2,rumus_kondisi2,rumus_kondisi3))

Selanjutnya masing-masing parameter dalam fungsi IF bertingkat di atas, maka kita bisa menggantinya dengan rumus per masing-masing kondisi, sehingga rumus akhir dapat dituliskan sebagai berikut.

=IF(B2=1,MIN(1,G2)*1.5+MAX(0,G2-1)*2,IF(B2=2,MIN(7,G2)*2+MIN(MAX(0,G2-7),1)*3+MAX(0,G2-8)*4,MIN(5,G2)*2+MIN(MAX(0,G2-5),1)*3+MAX(0,G2-6)*4))

Dengan contoh rumus di atas, kita bisa menghitung berapa faktor pengali jumlah jam lembur dibayar. Selanjutnya hasil dari rumus tersbut dapat dikalikan dengan upah sejam untuk menghasilkan nilai jumlah upah lemburnya.

Berikut screenshot penerapan rumus akhir beserta hasilnya:

Contoh Rumus Excel Menghitung Jam Lembur dan Upahnya


Catatan: Dalam pembahasan ini dititik beratkan pada pembahasan rumus No 3 (pada kolom H) karena saya fikir merupakan formula yang memerlukan kerutan kening lebih kuat dibandingkan ketiga rumus lainnya.

Demikian pembahasan mengenai cara menghitung jam lembur dibayar dengan bantuan fungsi IF, MIN dan MAX. Semoga mudah memahaminya dan semoga bermanfaat.

Artikel terkait:





4 comments:

  1. Rumus nomor 1: =IF(D2="",E2-C2,E2-D2). Di saya malah error rumusnya. tanda double petik dua tersebut fungsinya buat apa ya???

    ReplyDelete
  2. Bagaimana klo datang dan pulangnya tidak sesuai jadwal (bs lebih ato kurang)

    ReplyDelete
  3. Bagaimana bila karyawan datang di jam ke o, contohnya bila karyawan diharuskan datang lebih awal dari jam kerja masuk normal, misalnya si A datang masuk kerja pada jam 05:30 dan pulang jam 18:00 sedangkan jam kerja normal adalah jam 07:00 sd 16:00

    ReplyDelete
  4. waiting reply for fandi's question nih, kak. mengalami hal yg sama...

    ReplyDelete

Terimakasih sudah berkunjung, Silahkan berkomentar.