coretan tentang autocad dan excel

February 13, 2011

[GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004)

Filed under: Excel — Tags: , — cadex @ 13:25
Referensi : Keputusan Menteri Tenaga Kerja & Transmigrasi NOMOR KEP. 102/MEN/VI/2004:
WAKTU KERJA LEMBUR DAN UPAH KERJA LEMBUR
Platform : Excel
Lokasi File : ada di sini

Mengacu ke pasal 11:

Cara perhitungan upah kerja lembur:

Cara perhitungan upah kerja lembur sebagai berikut :

a. Apabila kerja lembur dilakukan pada hari kerja :
a.1. untuk jam kerja lembur pertama harus dibayar upah sebesar 1,5 (satu setengah) kali upah sejam;
a.2. untuk setiap jam kerja lembur berikutnya harus dibayar upah sebesar 2(dua) kali upah sejam.

b. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 6 (enam)
hari kerja 40 (empat puluh) jam seminggu maka :
b.1. perhitungan upah kerja lembur untuk 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.
b.2. apabila hari libur resmi jatuh pada hari kerja terpendek perhitungan upah lembur 5 (lima) 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.

c. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 5 (lima)
hari kerja dan 40 (empat puluh) jam seminggu, maka perhitungan upah kerja lembur untuk 8 (delapan) jam
pertama dibayar 2 (dua) kali upah sejam, jam kesembilan dibayar 3(tiga) kali upah sejam dan jam kesepuluh dan
kesebelas 4 (empat) kali upah sejam.

Pada contoh hitungan kali ini akan menggunakan asumsi 5 (lima) hari kerja dan 40 jam seminggu. Sehingga dari pasal tersebut yang digunakan adalah ayat a.2 dan c.

Persiapan dan tahapan perhitungan:

1. Buat list hari libur Nasional di sheet tersendiri.
Contoh List hari libur Nasional Tahun 2011 (pada contoh, cuti bersama tidak saya masukkan)
image
Block (select/pilih) range A1:A15, kemudian dari menu [insert], pilih [Define].
setelah diklik [OK], maka ‘Defined Name Range’ dengan nama [LiburNasional] sudah terdefinisi di workbook aktif
2. Contoh spreadsheet dengan jumlah jam kerja yang akan dihiutung jam kerjanya:
imageAgar di kolom [A], ditampilkan nama hari dalam bahasa indonesia, ganti format number di kolom [A] menjadi:
imagedi cell [C3], masukkan rumus
=SUMPRODUCT((LiburNasional=A3)*1)+(WEEKDAY(A3,2)>5)
a. Keterangan dari rumus SUMPRODUCT((LiburNasional=A3)*1) adalah, apabila tanggal di cell [A3] adalah libur nasional, maka akan menghasilkan nilai 1 (satu).

b. Sedangkan WEEKDAY(A3,2)>5 akan TRUE atau bernilai 1, jika tanggal di [A3] adalah hari Sabtu atau Minggu.

Jika kondisi a dan b dijumlahkan , maka untuk hari libur akan bernilai > 0, sedangkan untuk bukan hari libur bernilai = 0.

Copy rumus di [A3] ke bawah

3. Kolom [D2:G2] adalah angka faktor pengali lembur yang telah ditentukan sesuai dengan Pasal 11 ayat a.2 dan c.
Sedangkan kolom [H] adalah hasil dari penjumlahan faktor pengali lembur dari kolom [D2:G2]
4. Menghitung faktor pengali lembur di hari kerja biasa (bukan hari libur)
Asumsi 5 (lima) hari kerja dan 40 jam seminggu, maka jam kerja tiap hari yang tidak dihitung lembur adalah 8 jam/hari
Misal:
[B3]= jumlah jam kerja/hari
[C3]=0 untuk hari kerja, dan [C3]>0 untuk hari libur.
[D2],[E2],[F2] dan [G2]=faktor pengali 1.5, 2.0, 3.0 dan 4.0Maka Pasal 11 ayat a jika ditulis dalam rumus Excel:

a. Apabila kerja lembur dilakukan pada hari kerja =IF(C3=0,ke a.1,ke langkah 5)
a.1. untuk jam kerja lembur pertama harus dibayar upah sebesar 1,5 (satu setengah) kali upah sejam; IF(B3>8,1)*D$2
Rumus di gabung
=IF(C3=0,IF(B3>8,1))*D$2
a.2. untuk setiap jam kerja lembur berikutnya harus dibayar upah sebesar 2(dua) kali upah sejam =IF(C3=0,IF(B3>9,B3-9))*E$2
5. Pasal 11 ayat c:

c.1. perhitungan upah kerja lembur untuk 8 (delapan) jam pertama dibayar 2 (dua) kali upah sejam =MIN(B3,8)*E$2
rumus MIN(B3,8) untuk antisipasi, jika seandainya ada yang masuk di hari libur tetapi kurang dari 8 jam
c.2. jam kesembilan dibayar 3(tiga) kali upah sejam =IF(B3>=9,1)*F$2
c.3. jam kesepuluh dan
kesebelas 4 (empat) kali upah sejam
=IF(B3>=10,B3-9)*G$2
6. Rumus di [D3], [E3], [F3] dan [G3]
[D3]=IF(C3=0,IF(B3>8,1))*D$2
[E3]=IF(C3=0,IF(B3>9,B3-9),MIN(B3,8))*E$2  ‘gabungan fungsi a.2 dan c.1
[F3]=IF(C3>0,IF(B3>=9,1))*F$2
[G3]=IF(C3>0,IF(B3>=10,B3-9))*G$2
7. [H3]=SUM(D3:G3)
8. Jika upah perjam adalah 15000, maka [I3]=15000. Rumus di
[J3]=H3*I3
9. Copy rumus di [D3:J3] ke bawah untuk menghitung upah lembur di hari yang lain…
10. Pengecekan rumus perhitungan
Masukkan rumus di [K3]
=B3=(SUMPRODUCT(D3:G3,1/$D$2:$G$2)+IF(C3=0,MIN(B3,8)))
Kemudian copy ke bawah.
Jika nilai-nilai dikolom [K], bernilai TRUE, maka perhitungan faktor pengali lembur sudah benar.
11. Jika ingin disederhanakan, rumus di [H3] bisa dirubah menjadi (revisi.01 14 Februari 2011)
[H3]=N(IF(C3=0,IF(B3>8,1.5+(B3-9)*2),IF(B3>0,MIN(B3,8)*2+IF(B3>=9,3)+IF(B3>=10,(B3-9)*4))))

==silahkan mencoba==
Untuk 6 (enam) hari kerja silahkan click di sini

10 Comments »

  1. wah mantep juragan… ada rumus interval untuk perhitungan overtime ndak gan???
    yg bisa ngitung interval kalo lebih dari 4 jam dikurangi 1 jam istirahat dan yg lembur melebehi jam 12 malam.. karena udah beda hari…
    mohon pencerahannya gan..

    Comment by Jay Rindra Kurokuro — August 27, 2012 @ 22:03

    • maaf belum ada krn belum pernah meng=aplikasikan.

      Comment by cadex — September 1, 2012 @ 06:59

  2. makasi ilmunya…saya udah coba tapi kalo hari libur nasional jatuh di hari minggu nilai di kolom HARI LIBUR jadi 2 nah itu gimana ? ngaruh ga

    Comment by nananana — October 5, 2012 @ 16:01

    • dicoba saja, sepertinya sih nggah ngaruh di perhitungan lembur

      Comment by cadex — October 8, 2012 @ 09:57

  3. contoh diatas kolom JAM belum dikurangi jam istirahat siang n jam istirahat lembur ya

    Comment by nananana — October 5, 2012 @ 16:27

    • masukkan jam setelah dikurangi jam istirahat (murni jam kerja)

      Comment by cadex — October 8, 2012 @ 09:56

  4. ga bisa di download bos…

    Comment by riski — September 30, 2013 @ 06:09

    • click linknya.
      saat ditampilkan web skydrive, click menu download. barusan saya coba bisa

      Comment by cadex — September 30, 2013 @ 09:00

  5. mas mau tanya & minta bantuan nih..
    saya ingin membuat rumus tapi bingung..
    contoh:
    apabila 08:47 dibuatkan rumus agar menjadi 09:00 dan apabila 08:40 dibuatkan rumus menjadi 08:00 dengan ketentuan:
    apabila menitnya kurang dari 45 maka dibulatkan menjadi 00
    apabila menitnya lebih dari 45 maka ditambah 1 jam, contoh 08:47 karena menitnya lebih dari 45 maka ditambah 1 jam sehingga menjadi 09:00
    rumusnya bagaimana ya mas? mohon bantuannya ya..
    terimakasih

    Comment by Aditya — October 30, 2014 @ 10:54

    • coba rumus ini:
      =IF(MINUTE(A4)>=45,HOUR(A4)+1,HOUR(A4))/24

      masukkan jam yang dimaksud di A4 dengan syarat dimasukkan dengan format jam misal 08:47 bukan 08.47

      Comment by cadex — October 30, 2014 @ 11:15


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: