sumproduct – coretan tentang autocad dan excel

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

Pada posting [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) telah diuraikan cara menghitung lembur atau menentukan faktor pengali lembur untuk 5 hari kerja. Kali ini akan diuraikan cara menghitung faktor pengali lembur untuk 6 hari kerja. Hitungan kali ini memasukkan hari kerja adalah Senin s.d Sabtu dengan asumsi hari Sabtu adalah hari pendek. Jam kerja normal senin sampai sabtu adalah 7 jam, sedangkan untuk hari pendek adalah 5 jam. Kutipan dari Keputusan Menteri Pasal 11 :

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.

ayat a, b atau text berwarna biru adalah sebagai dasar untuk perhitungan lembur. Seperti posting sebelumnya [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004), list hari libur dibuat terlebih dahulu. List hari libur di posting kali ini menggunakan named range dengan type dynamic, bukan static range yang telah dijelaskan sebelumnya. Static Named range mengharuskan kita untuk memlilih range terlebih dahulu sebelum mendefinisikan range, dimensi range (records) akan bertambah jika penambahan data ada di tengah static range atau dengan cara insert rows. Sedangkan Dynamic Named Range, range akan otomatis berubah dimensinya atau bertambah data secara otomatis jika record ditambahkan ataupun diiinsert. Tahapan-tahapan yang dibuat di bawah dikerjakan engan Excel 2007, jika Anda menggunakan excel versi lain, silahkan disesuaikan. Tahapan pembuatan dynamic range adalah :

Buat list daftar hari libur di sheet tersendiri, misalnya daftarnya sebagai berikut :

Pada menu group [Formula], pilih [Define Name]

Masukkan nama range, misal "_LiburNasional" Refers to: =OFFSET($B$1,1,-1,COUNT(A:A),1) maksud dari rumus di atas adalah :— membuat range dengan acuan $B$1, turun 1 baris, kemudian ke kiri 1 kolom. Hasil dari perintah ini adalah $B$2 — dilanjutkan dari B2, membuat range dengan tinggi sebanyak angka atau tanggal di kolom A:A atau COUNT(A:A) dengan lebar 1 kolom. Jika tanggal hari libur di kolom A ada 20 tanggal, maka akhir dari perintah ini adalah $B$2:$B$20

Buat tabel kode hari kerja yaitu kode hari yang membedakan hari normal, sabtu, minggu, hari libur nasional dan hari libur di hari pendek (sabtu)

Buat ‘static named range’ dengan nama yang telah didefinisikan di kolom ‘named range’:

— block [J2:K5]

— dari menu [Formulas], pada group [Define Names], pilih [Create from Selection]

— Pada pilihan ‘Create names from values in the’ , centang (check) hanya di [Right Column]

Memberi kode hari di list tanggal absensi. misal tabel disusun sebagai berikut:

Revisi 10 April 2014

Kolom A dan B diisi manual, sedangkan untuk kolom C:H diisi dengan rumus excel. Rumus untuk kolom C:H adalah: Catatan: rumus atau formula ditulis dalam satu baris dalam excel

Cells	Rumus	Keterangan
[C2]	=IF(COUNTIF(_LiburNasional,A2)>0,IF(WEEKDAY(A2,2)=6,3,2),IF(WEEKDAY(A2,2)=6,1,IF(WEEKDAY(A2,2)=7,2,0)))	menentukan kode hari berdasarkan tanggal di A2
[D2]	=(C2=Normal)(B2>7)+(C2=Sabtu)(B2>5)	jam lembur x 1.5
[E2]	=IF((C2=Normal)(B2>8),B2-8,IF((C2=Sabtu)(B2>6),B2-6,IF(C2=LiburMinggu,IF(B2<7,B2,7),IF(C2=SabtuLibur,IF(B2<5,B2,5)))))+0	jam lembur x 2
[F2]	=(C2=LiburMinggu)(B2>7)+(C2=SabtuLibur)(B2>5)	jam lembur x 3
[G2]	=IF((C2=LiburMinggu)(B2>8), B2-8,IF((C2=SabtuLibur)(B2>6),B2-6))*1	jam lembur x 4
[H2]	=SUMPRODUCT(D2:G2,$D$1:$G$1)	total faktor pengali
	Copy Rumus [C2:H2] ke bawah..

Silahkan download file revisi untuk contoh sample aplikasi perhitungan gaji lengkap dengan hitungan lembur untuk 6 hari kerja. File hasil download adalah simulasi perhitungan dari input daftar karyawan & unit price gaji, list hari libur, form resume absensi dan contoh form slip gaji. Jika ada kesalahan perhitungan, jangan sungkan-sungkan untuk memberi masukan.

==selamat mencoba==

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:

Buat list hari libur Nasional di sheet tersendiri.
Contoh List hari libur Nasional Tahun 2011 (pada contoh, cuti bersama tidak saya masukkan)

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

Contoh spreadsheet dengan jumlah jam kerja yang akan dihiutung jam kerjanya:

Agar di kolom [A], ditampilkan nama hari dalam bahasa indonesia, ganti format number di kolom [A] menjadi:

di 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

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]

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

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

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

[H3]=SUM(D3:G3)

Jika upah perjam adalah 15000, maka [I3]=15000. Rumus di
[J3]=H3*I3

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