coretan tentang autocad dan excel

April 10, 2014

[XLS-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 2 (Revisi 10 April 2014)

Filed under: Excel, Project Management — Tags: , , — cadex @ 16:53

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==

30 Comments »

  1. gan.. ane mentok disini nih
    tolong bantu ya?
    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

    Comment by bayu — January 30, 2013 @ 22:58

    • mentoknya gimana?
      donwload aja spreadsheetnya kemudian dipelajari..

      Comment by cadex — January 31, 2013 @ 11:32

    • Terima kasih sebelumnya, tulisan saudara sangat membantu saya …. mohon kirim lewat email ke saya nana@d-panasia,com file yang sudah jadi terima kasih sebelumnya, alasan ini saya sampaikan saya sudah mempelajari dan mempraktekannya tetapi tidak sesuai harapan sampai saat ini sdh 3 bulan mentok terus, terima kasih salam. Nana

      Comment by nana supriyatna — June 21, 2014 @ 08:43

      • sudah didownload filenya? hasil yang diharapkan seperti apa?

        Comment by cadex — June 23, 2014 @ 11:03

  2. Gan, mau tanya nih, yg kolom B pada “Jam” itu banyak nya jam kerja atau gimana gan?
    Terimakasih sebelumnya.

    Comment by DinkdinkLee — February 27, 2013 @ 15:02

    • aktual jam kerja.
      misal masuk jam kerja jam 8.00 pulang jam 17:00 ada 9jam istirahat 1jam, maka aktual jam kerja (9-1)jam=8jam/hari

      Comment by cadex — February 27, 2013 @ 15:59

      • Ok trimakasih gan.

        Comment by Dinkdink Lee — March 1, 2013 @ 14:47

  3. Gan, numpang nanya ya.. kalau hari normal kan perhitungan 1jam pertama x 1,5; jam berikutnya dikali 2. kok rumusnya tidak match ya???
    Tolong bantuannya… thanks…

    Comment by Ratna Santoso — August 7, 2013 @ 17:20

    • bisa beri contoh di mana salahnya? mungkin bisa dihitung manual dulu..

      Comment by cadex — August 7, 2013 @ 19:24

      • =IF(C2=SabtuLibur,IF(B2<5,B2,5),IF(C2=LiburMinggu,IF(B25,B2>7)))*1
        (kalau untuk hari lembur normal seharusnya jam ke 2 dan seterusnya hanya di kali 2)

        thanks…

        Comment by Ratna Santoso — August 7, 2013 @ 20:07

      • sudah dicoba hitung manual tanpa excel dan dibandingkan hasilnya? apakah sama?
        asumsi anda sdh download file, coba anda ganti di cell [B3] di sheet [Tahapan] dengan angka 10 yaitu kerja 10jam di hari normal, maka perhitungan lemburnya sesuai peraturan adalah (1jam x 1.5) + (1jam x 2) + (1jam x 3) = 6.5jam . hasilnya sama dengan nilai cell di H3.

        jadi perkalian *1 di akhir rumus bukan faktor pengali lembur, faktor pengalinya ada di baris [D1:H1]

        Comment by cadex — August 8, 2013 @ 01:58

      • kalau 10 Jam, berarti 7 jam normal dan 3 jam lembur. sehingga sesuai perhitungan lembur disnaker adalah (1jam x 1,5) + (2jam x 2) = 3,5 jam.. Mohon koreksi…

        Comment by Hendra — April 10, 2014 @ 10:26

      • Mas Hendra, terimakasih koreksinya akan saya koreksi lagi logic programnya. Bukankah (1jam x 1,5) + (2jam x 2) = 5,5 jam?

        Comment by cadex — April 10, 2014 @ 16:01

  4. Bang, saya mau tanya kalo untuk perhitungan 5 hari kerja bagaimana ? Karena dari data yang saya download (GEN-PMG-04R1) hanya ada timesheet & libur nasional saja. sedangkan untuk yang lainnya tidak ada. Terima Kasih

    Comment by lisna dzikra — January 8, 2014 @ 14:53

    • file yang didownload adalah untuk hitungan 5hari kerja. “untuk yang lainnya” maksudnya apa?

      Comment by cadex — January 8, 2014 @ 15:06

      • untuk yang lainnya seperti slip gaji dan nama-nama karyawan. Bisa minta link untuk perhitungan 5 hari ?

        Comment by lisna dzikra — January 9, 2014 @ 08:35

      • silahkan dikembangkan sendiri dengan file yang sudah ada…

        Comment by cadex — January 9, 2014 @ 08:43

  5. Mencoba berbagi untuk perhitungan upah lembur dengan sistem 5hari kerja dalam seminggu, sehari 8 jam kerja + 1jam istirahat menggunakan Excel.
    semoga bermanfaat.

    Comment by andrianus wibowo (@andrianuswm) — March 10, 2014 @ 23:40

    • thanks for sharing

      Comment by cadex — March 11, 2014 @ 06:46

  6. mas, untuk yang perhitungan 6 hari kerja,
    apakah beda hitungan lembur untuk shift siang dan malam?
    kalo gak salah shift siang 40 jam, untuk malam 35..
    saya kerja di perusahaan tambang, soalnya ditempat saya tiap minggu ganti shift,
    gimana ya rumusnya untuk yang masuk shift malam?

    shift siang
    senin-jumat 5.5

    shift malam
    senin-jumat 7.5

    Comment by Iwan — May 31, 2014 @ 06:16

    • sesuai dengan judulnya, dasar perhitungan upah lembur mengacu ke Kep Men. NOMOR KEP. 102/MEN/VI/2004.

      Terus terang untuk perusahaan tambang, saya belum pernah melakukan perhitungan. Yang aku tahu untuk perusahaan tambang ada di PER-15/MEN/VII/2005 ” WAKTU KERJA DAN ISTIRAHAT PADA SEKTOR USAHA PERTAMBANGAN UMUM PADA DAERAH OPERASI TERTENTU”

      Comment by cadex — June 2, 2014 @ 10:38

  7. mas, mau tanya nih bagaimana caranya untuk menampilkan atau membuat rumus {=SUM((C5:AH5>0)*1)}?
    saya sudah coba menggunakan tanpa tanda {} yaitu =SUM((C5:AH5>0)*1) tapi hasilnya malah #VALUE, begitupula dengan rumus ini =SUM(($C$1:$AH$1=Normal)*($C9:$AH9>7)+($C$1:$AH$1=Sabtu)*($C9:$AH9>5))
    #pada sheet RESUME, AI, AJ, AK, AL dan AN
    apakah ada cara khusus mas?
    tolong pencerahannya ya mas.. terimakasih

    Comment by Aditya — October 30, 2014 @ 08:49

    • rumus yang ada tanda {} merupakan rumus formula array.

      untuk memunculkan tanda tersebut saat selesai menuils rumus jangan menekan tombol ENTER tetapi kombinasi tombol CONTROL+SHIFT+ENTER

      Comment by cadex — October 30, 2014 @ 09:09

  8. Mas, tolong penjelasannya Rumus yang untuk kode hari (C2), sebab kalau saya tambahkan kode hari nomor 4 untuk namedrangenya hari jumat (sebab jumat ada potongan lagi satu jam) itu tidak bisa ya mas?
    Mohon pencerahannya

    Comment by yogie — February 27, 2015 @ 11:52

    • bisa minta contoh perhitungan manualnya?

      Comment by cadex — March 9, 2015 @ 09:42

      • Misal :
        0 = normal = senin s.d kamis (basic 7jam)
        1 = sabtu (basic 5jam)
        2 = minggu dan libur nasional (langsung lembur 2x)
        3 = jumat (basic 7jam. Tp ada pengurangan 1 jam karena istirahatnya lebih awal)

        Untuk formula kode hari dan lemburannya seperti apa ya mas.
        Terimakasih.

        Comment by yogie — March 9, 2015 @ 09:53

  9. Mas zainul mohon pencerahan, kalau saya ganti nama-1 pada sheet namakaryawan, mengapa yang terbaca pada kolom slip adalah nama-9, sehingga formula cross cek yang diberikan mas zainul menampilkan tanda merah [CEK], saya sudah coba edit formula tabel untuk _NamaKaryawan namun belum berhasil, terima kasih buat ilmu formula barunya dalam file ini.

    Comment by Ary — April 8, 2015 @ 17:12

    • apakah nama yang diganti ada sheet [resume]? nama di slip gaji diambil dari sheet [resume]

      Comment by cadex — April 21, 2015 @ 16:46

  10. mas untuk di sheet tahapan itu gunanya untuk apa ya ?

    Comment by permana — September 2, 2015 @ 09:46

  11. Tanggal Jam KodeHari 1.5 2 3 4 X
    Jumat, 15 Mei 15 10 0 1 2 0 0 5.5
    Selasa, 19 Mei 15 10 0 1 2 0 0 5.5
    Minggu, 17 Mei 15 10 2 0 7 1 2 25
    Senin, 17 Agust 15 10 2 0 7 1 2 25
    Selasa, 18 Agust 15 10 0 1 2 0 0 5.5
    Rabu, 19 Agust 15 10 0 1 2 0 0 5.5

    menentukan apa ??

    Comment by permana — September 2, 2015 @ 09:47


RSS feed for comments on this post.

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

Blog at WordPress.com.

%d bloggers like this: