| Referensi | : | [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) [XLS-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 2 |
| Platform | : | Microsoft Excel |
| Download | : | Download |
Pada tulisan sebelumnya telah diuraikan tahapan untuk menghitung lembur dengan kondisi 5 (lima) hari kerja/minggu dan 6 (enam) hari kerja/minggu dengan input jam kerja yang sudah diketahui. Pada tulisan ini akan saya sharing template yang pernah saya buat untuk aplikasi di suatu proyek dengan kondisi yang lebih komplek yaitu:
- 1. Data yang dimasukkan adalah [Jam Masuk] dan [Jam Keluar].
- 2. Jam Istirahat bisa diatur fleksibel atau bisa dirubah.
- 3. Perhitungan lima(5) hari kerja, jika karyawan bekerja di kantor pusat dan menggunakan perhitungan 6 (enam) hari kerja jika bekerja di lapangan
- 4. Slip gaji sudah dilengkapi dengan perhitungan BPJS dan Pajak Penghasilan (PPH21).
Berikut tutorial cara penggunaan spreadsheet excel yang sudah di download:
1. Memasukkan Setting Hari Libur di Sheet [hr_libur]
Masukkan tanggal hari libur di kolom [tgl] atau kolom [A] dengan format tanggal yang sesuai dengan settingan di windows. Contoh setting tanggal di komputer saya yang terlihat di task bar windows adalah 
, maka untuk tanggal 30 Mei 2018 dimasukkan dengan format 5/30/2018 atau 5/30/18.
Setelah memasukkan tanggal sesuai dengan format yang benar, masukkan juga keterangan hari libur di kolom [Keterangan]. Bagian kolom [No] tidak perlu diisi karena kolom ini akan terisi otomatis tiap ada penambahan record atau dta baru.
2. Memasukkan Setting Jam istirahat di sheet [jm_istrht]
Kolom [No], [Hari] dan [Kode Hari] berisi formula. Input jam kerja dimasukkan di kolom:
[Rest01 Start], [Rest01 End] : untuk jam istirahat pertama
[Rest02 Start1], [Rest02 End] : untuk jam istirahat kedua
[Rest03 Start], [Rest03 End] : untuk jam istirahat ketiga
Jam dimasukkan dengan format hh:mm, dalam format ini jika akan memasukkan jam 1 siang atau 1:00 PM maka jam dimasukkan dengan format 13:00 demikain juga untuk jam 11:00 pm dimasukkan dengan format 23:00.
Format hh:mm ini juga berlaku saat pengisian jam masuk kerja dan jam keluar kerja.
3. Memasukkan Isian di sheet [ts] dan [slip]
Jika diinginkan format tanpa detail perhitungan gaji, dapat mengunakan format di sheet [ts] sedangkan jika diinginkan input jam kerja disertai dengan detail perhitungan gaji dapat menggunakan format di sheet [slip].
Pada prakteknya di lapangan, saya menggunakan 2 (dua) sheet tersebut untuk aplikasi yang berbeda. Sheet [ts] diberikan ke karyawan digunakan untuk mendapatkan approval dari atasan, sedangkan sheet [slip] digunakan untuk menghitung gaji atau sebagai slip gaji.
Masing-masing sheet [ts] dan [slip] terdapat kolom [Q]:[AE] yang berisi rumus bantu untuk perhitungan timesheet, Anda bisa hide kolom ini saat pencetakan atau distribusi form ke karyawan. Jangan dihapus atau dimodifikasi rumus ini, kecuali Anda benar-benar memahami isi dan maksud dari rumus tersebut.
Format sheet [ts]:
Isikan ID, Nama dan Posisi atau bila diperlukan bisa ditambahkan nama supervisi, department atau isian lain pada area K2:O3
Pilih Bulan dan Tahun di cell [D2] dan [E2], maka kolom [Tgl] dan [Hari] akan terisi otomatis, kolom berwarna hijau berisi formula, sehingga tidak perlu diisi kolom hijau akan otomatis terupdate jika ada perubahan tanggal termasuk perubahan warna huruf jika tanggal bertepatan dengan hari libur, jumat, sabtu dan minggu.
Kolom yang perlu diisi manual adalah:
| Nama Kolom | Keterangan |
| Kode Hari Kerja | Masukkan angka 5 jika bekerja untuk 5 hari kerja/minggu dan masukkan angka 6 jika untuk bekerja 6 hari kerja / minggu |
| Masuk , Keluar | Isikan jam masuk, jam keluar sesuai dengan format tanggal di windows |
| Kolom [Durasi] akan terisi otomatis jika jam masuk dan jam keluar sudah terisi. Format durasi adalah dalam satuan desima sehingga jika durasinya adalah 8 jam 30 menit, maka masukkan angka 8.5 | |
| Koreksi | Isikan koreksi jam kerja jika diperlukan dalam satuan desimal. Misalnya dalam ada koreksi penambahan jam setengan jam atau 00:30 maka dimasukkan 0.5 atau sebaliknya ada pengurang jam 00:30, maka dimasukkan -0.5 |
Sedangkan tampilan untuk sheet [slip]:
Ada tambahan input yaitu:
| Nama Input | Keterangan |
| Gaji Dasar | Masukkan gaji pokok atau gaji dasar /bulan |
| Tunjangan Tetap | Masukkan Tunjangan tetap /bulan |
| Rate Lembur | Masukkan unit rate lembur / jam. “maaf tulisan di template masih /bulan, mohon untuk dikoreksi /jam” |
| Tunj. Makan | Masukkan tunjangan makan /hadir |
| Tunj. Transport | Masukkan tunjangan transport /hadir |
| Tunj. Kehadiran | Masukkan tunjangan kehadiran /hadir |
| PTKP Karyawan | Masukkan golongan PTKP (Penghasilan Tidak Kena Pajak) karyawan sesuai dengan peraturan yang berlaku. |
Detail perhitungan gaji disertai dengan perhitungan BPJS dan PPH21 adalah:
Silakan dicoba dan mohon koreksinya jika ada kesalahan.
Terimakasih telah berkunjung di blog saya.
coretan tentang autocad dan excel 
Rate Lembur Rp23.121 /bulan
DI RUMUS PAKE ROUND (=ROUND(SUM(H2:I3)/173;0)) MAKSUD DARI 173 ITU APA YA? bisa mohon dijelaskan….. terimakasih..
maaf itu maksudnya Rp23.121/jam
173 jam/bulan=40 (jam/minggu) x 52 (minggu/tahun) / 12 (bulan/tahun)
maaf pak bila perhitungan gaji tanggal 15 ke 15 bulan berikutnya bagaimana ya?
sama saja kan…cuma geser tanggal saja tidak berpengaruh cara hitungnya
Hari minggu atau tgl merah masuk kok harinya masih ikut di hitung apa gak hanya jam kerjanya aja pak
tetap dihitung menurut karena yang membedakan hanya di cara hitung lemburmya kalo menurut peraturan
Gak ada yutub ya pak cara bikinnya
nggak ada…
Bagi nomer wa dong pak
Rumus Jam lembur yang di tabel Jam ke 9 sama jam ke 10 di hari normal kok gak langsung ngerumus seharusnya kan jam yang ke 9 itu 1.5 mas dan jam yang 10 seharusnya ngerumus 2 jam gak gitu ta
perhatikan isian di kolom B untuk kode hari kerja. jam ke-9 di kali 1.5 untuk “5 hari kerja”, sedangkan jika “6 hari kerja” dimulai dari jam ke-8.
sudah saya tulis “Masukkan angka 5 jika bekerja untuk 5 hari kerja/minggu dan masukkan angka 6 jika untuk bekerja 6 hari kerja / minggu”
coba ganti isian kode hari kerja di kolom B, apakah rumusnya sudah sesuai?
Udah pak tapi tetep dicolom perhitungan lembur keluar angka 1 kok gak langsung 1.5. di gambar postingan uda kelihatan pak gambar atas pak….
5 tgl 02 hari rabo masuk 07 keluar 20 durasi jam 13 jam istirahat 2jam
Jam kerja 12 jam normal 8jam di tabel perhitungan lembur colom 1.5 tetep terisi 1 dan di colom 2 tetep terisi 2 pak itu abis saya coba dan juga lihat gambar di atas pak perhitungan lembur
kolom L:O memang belum dikalikan factor pengali x1.5, x2, x3 dan x4 dengan maksud untuk cross cek jumlah jam kerja sebelum dikalikan factor pengali. Silakan donwload lagi file templatenya di https://onedrive.live.com/embed?cid=7B3122134B7F51A8&resid=7B3122134B7F51A8%2111310&authkey=AJAJT5HFSY6edwk
jumlah jam lembur setelah factor pengali ada di L41=SUMPRODUCT(L40:O40,L7:O7)
terimakasih atas koreksinya
pak untuk haru sabtu jika ingin mengganti dengan 7 jam kerja bagaimana ya soalnya sabtu rumusnya 5 hari kerja
Terus terang sunggu bermanfaat buat saya …. sampai2 saya cari bikin dari awalnya gak ketemu maklum saya blajar audidak dari google dan yutub. Sebenernya malu tanya2 terus tapi karna saya pingin bisa harus saya tanyakan ?
Oiya pertanyaan saya gimana caranya menemukan rumus hari kalau tgl merah atau libur nasional atau sabtu minggu gak terhitung harinya yang terhitung hari efektis aja soalle kalau hari besar atau tgl merah itu udah langsung di hitung lemburanya saja . Karna saya ingin perhitungan hari kerja tidak global melainkan harian tapi tanpa tgl merah terima kasih atas file maaf mungkin keterangan saya ada yank salah atau terlalu minta aneh dengan adanya ini saya sangat2 berterima kasih
Kenapa kok gak bisa di hyperlink pak ya
bisa kok walau aku pakai hape
Selamat pagi pak cadex, salam hormat, perkenalkan saya ridho dari Semarang.
Dengan berbagi ilmu yang bermanfaat semoga selalu dimudahkan dan dilancarkan rezekinya. aamiin
Sebelumnya maaf saya sedikit bertanya karena saya tidak memahami cara baca rumus excel, dengan rumus ini hari libur nasinonal, dan hari minggu sudah sesuai otomatis termasuk sebagai jam/waktu lembur kerja,
pertanyaannya:
bagaimana cara menambahkan hari sabtu termasuk jam/waktu lembur?
=IF(AND(K4″Minggu”;N4>TIME(12;0;0);L4=”Ya”);P4-1;IF(AND(K4″Minggu”;N4<=TIME(12;0;0);L4="Ya");P4;IF(AND(K4″Minggu”;P4>8;L4″Ya”);P4-1-7;IF(AND(K4=”Minggu”;M4″”);P4-1;0))))
colom
L = Hari Nasional
K = Hari
M = Masuk Jam Kerja
N = Pulang Jam Kerja
P = Total Jam Kerja
Terimakasih, salam.
coba baca di bagian keterangan “Masukkan angka 5 jika bekerja untuk 5 hari kerja/minggu dan masukkan angka 6 jika untuk bekerja 6 hari kerja / minggu”
Selamat pagi pak cadex, terimakasih.
=IF(AND(K4″Minggu”;N4>TIME(12;0;0);L4=”Ya”);P4-1;IF(AND(K4″Minggu”;N48;L4″Ya”);P4-1-7;IF(AND(K4=”Minggu”;M4″”);P4-1;0))))
Kalo dari rumus ini, bagaimana caranya menambahkan hari sabtu sebagai hari libur?
Terimakasih, salam.
Selamat pagi pak cadex, terimakasih.
=IF(AND(K4″Minggu”;N4>TIME(12;0;0);L4=”Ya”);P4-1;IF(AND(K4″Minggu”;N48;L4″Ya”);P4-1-7;IF(AND(K4=”Minggu”;M4″”);P4-1;0))))
Kalo dari rumus ini, bagaimana caranya menambahkan hari sabtu sebagai hari libur?
Terimakasih, salam.
apakah formula di atas mengacu ke rumus yang ditulis di halaman ini? Jika tidak , maka pertanyaan tidak ada hubungannya dengan isi halaman ini
kalau buat 50 karyawan bagaimana bikinnya atau bagai mana cara Copa nya ??
buka file, save as nama file samadengan nama karyawan. begitu seterusnya
bagaimana cara merubah hari sabtu menjadi jam normal 7 jam, pada kolom kode hari kerja sudah saya ganti dengan angka 6 tapi hari sabtu masih tetap pada 5 jam kerja sehari