Menghitung Jam Kerja Karyawan Disini saya coba menjelaskan bagaimana menghitung jumlah jam kerja karyawan dengan rumus-rumus excel. Dalam kasus disini sudah kita tetapkan jam masuk kantor dan jam keluar kantor, dan jam disini bisa disesuaikan dengan kebutuhan kebutuh an kantor yang bersangkutan. Jadi Jad i jam masuk karyawan akan dibandingkan dengan jam masuk kantor sehingga dari perbandingan ini akan muncul status, status tersebut bisa telat atau tidak. Begitu pula dengan jam pulang karyawan akan dibandingkan dengan jam pulang kantor sehingga disini muncul suatu status yang berisi “pulang awal” dan “tidak”.
Seperti yang kita lihat bentuk tabel excel diatas, dibawah ini akan saya jelaskan satu persatu maksudnya. Cell B1 : berisi tanggal yang berlaku saat membuat absensi Cell B2 : jam masuk yang diberlakukan oleh kantor Cell B3 : jam keluar yang diberlakuakan oleh kantor Cel B4 : jumlah jam istirahat, disini tulis ’1:00′ , bukan berarti jam ‟1:00′ tetapi maksudnya maksudnya 1 jam. Kenapa formatnya harus begitu? Ini dimaksudkan agar komputer mengerti bahwa in i merupakan format jam, sehingga akan lebih mudah dalam melakukan perhitungan jam. Cell A8 : merupakan kode karyawan Cell B8 : merupakan nama karyawan Cell C8 : merupakan jam chek in karyawan Cell D8 : berisi rumus : =IF(C8>$B$2;”Telat”;”OK”), jadi disini menyatakan status check in, jika telat maka akan muncul status “Telat”, dan d an jika tidak akan muncul stasus “OK”. Cell E8 : merupakan jam chek out karyawan Cell F8 : berisi rumus: =IF(E8<$B$3;”Pulang Awal”;”OK”), menyatakan status check out, jika pulang lebih awal maka akan ak an muncul muncul status „Pulang Awal‟ jika tidak statusnya „OK‟. Cell G8 : berisi rumus =E10-C10-$B$4, ini merupakan kalkulasi (check in – in – check check out – out – istirahat), sehingga kita bisa menemukan berapa jam karyawan tersebut bekerja pada hari ini. Saya kira sampai disini dulu penjelasan saya, mudah-mudahan bisa bermanfaat
Menghitung Jam Kerja Karyawan Berdasarkan Shift Tulisan ini merupakan lanjutan dari artikel saya berjudul Menghitung Jam Kerja Karyawan. Pada artikel tersebut merupakan perhitungan standard. Artinya tidak ada perhitungan dengan jam kerja dengan memakai shift. Jadi setelah ada komentar yang masuk menanyakan hal ini maka saya lanjutkan dengan membikin perhitungan dengan memakai Shift dengan jumlah 2 ( Shift ) jam kerja. Dan kemungkinan bisa ditambah dengan 3 Shift kerja, dengan merubah sedikit modifikasi pada rumus-rumusnya. Dibawah ini merupakan contoh gambaran untuk 2 shift kerja :
Berbeda dengan tabel excel dari artikel saya sebelumnya, pada jam masuk dan jam keluar disini saya bagi dengan dua shift seperti yang telihat diatas. Dan ada tambahan kolom Shift pada tabel absensi (tabel utama). Cara kerjanya sama dengan yang lama cuma disini ada tambahan untuk rumus pada kolom IN Status, Out Status dan Jumlah (jam). Pada Cell E11 diisi dengan rumus : =IF(C11="I";IF(D11>$B$4;"Telat";"Ok"); IF(D11>$C$4;"Telat";"Ok"))
Pada Cell G11 diisi dengan rumus : =IF(C11="I";IF(F11<$B$5;"Pulang Awal";"Ok"); IF(F11<$C$5;"Pulang Awal";"Ok"))
pada Cell H11 diisi denganrumus : =F11-D11-$B$7
Statistik Absensi Karyawan Melanjutkan artikel saya yang terdahulu yang berjudul Menghitung Jumlah Jam Kerja Karyawan. Disini saya ingin mengembangkan kembali rumus-rumus tersebut dengan memasukan suatu variabel yaitu karyawan tidak masuk kerja. Tentunya sangat menyulitkan sekali kalau dikantor kita mempunyai banyak karyawan dan kita harus menghitung berapa jumlah karyawan yang tidak masuk kerja, untuk itu saya membuat suatu rumusan-rumusan yang sekiranya bisa mempermudah untuk melakukan perhitungan tersebut.( Perlu diketahui saya memakai excel office 7.) Dan dengan alasan tersebut pada artikel ini saya akan memasukan rumus-rumus perhitungan untuk : 1. Jumlah karyawan yang tidak masuk 2. Jumlah karyawan yang masuk tepat waktu 3. Jumlah karyawan yang masuk telat 4. Jumlah karyawan yang pulang tepat waktu 5. Jumlah karyawan yang pulang lebih awal Dengan mengetahui komposisi perhitungan diatas kita dapat menghitung absensi harian dan selanjutnya akan ditemukan perhitungan absensi selama satu bulan kerja. Kalau sudah mengetahui perhitungan-perhitungan tersebut tentunya dapat diambil kebijakan-kebijakan tertentu untuk meningkatkan kinerja karyawan. Sebenarnya saya disini saya juga ingin menjelaskan tentang fungsi Countifs() pada Excel Office 7 (untuk Excel 2003 fungsi ini tidak ada). Dam sarana yang paling tepat untuk contoh implementasi adalah dengan menghitung absensi karyawan ini, dengan alasan artikel yang sudah ada dikembangkan lebih lanjut. Countifs() adalah suatu fungsi perhitungan counter dengan dengan persyaratan yang majemuk. Artinya dalam suatu perintah kita bisa memberikan beberapa syarat-syarat kondisional secara bersamaan. Bentuk statementnya COUNTIFS(range_criteria_1; criteria_1;[range_criteria_2;criteria_2]) . Artinya kita dapat memasukan beberapa kriteria. Untuk lebih jelasnya kita contohkan seperti tabel dibawah :
Dari tabel yang terlihat bagaimana cara menghitung berapa jumlah laki-laki dan jumlah perempuan. Untuk menghitung jumlah laki-laki : =COUNTIFS(B3:B11;"L") menghasilkan nilai 5
Dan untuk menghitung jumlah perempuan ;
=COUNTIFS(B3:B11;"P") menghasilkan nilai 4
B3:B11 merupakan range yang akan kita hitung, dan “L” atau “P” kriteria jenis kelamin tertentu yang akan kita hitung. Dari rumus-rumus diatas masih merupakan perintah sederhana, karena belum memasukan dua kriteria sekaligus. Langkah selanjutnya agar mudah mengerti saya akan memasukan dua kriteria sekaligus, pertanyaan begini : berapa jumlah laki-laki yang berumur dibawah 30 tahun. Rumus yang dihasilkan : =COUNTIFS(B3:B11;"L";C3:C11;"<30") Menghasilkan nilai : 2 Karena cuma Adi dan Nono saja laki-laki yang berumur dibawah 30 tahun
C3:C11 merupakan range untuk kolom umur, dan “<30″ adalah kriteria dibawah 30. Dengan mengerti maksud dari fungsi diatas selanjutnya kita akan meng-implementasikannya pada Absensi Karyawan seperti tabel dibawah ini.
Tabel tersebut merupakan kembangan dari artikel saya di menghitung-jam-kerja-karyawan berdasarkan-shift. Cuma disini saya tambahkan untuk variable karyawan yang tidak masuk kerja supaya jumlah karyawan yang tidak masuk kerja bisa dihitung. Jadi aturan mainnya disini apabila jam masuk (Cell D12) diberi tanda “-” maka karyawan dianggap tidak masuk. Pada status Check in (cell E12) rumusnya sbb : =IF(D12="-";"-";IF(C12="I";IF(D12>$B$4;"Telat";"Ok"); IF(D12>$C$4;"Telat";"Ok")))
Dan pada Check Out Status (cell G12) rumusnya sbb : =IF(D12="-";"-";IF(C12="I";IF(F12<$B$5;"Pulang Awal"; "Ok");IF(F12<$C$5;"Pulang Awal";"Ok")))
Disini saya juga memasukan sedikit perhitungan Statistik, yaitu ditabel atas pojok kanan. Penjelasannya sbb : Total Karyawan : Merupakan jumlah karyawan yang terbagi dua Shift yang dihitung secara otomatis. Pada cell G3 diisi rumus : =COUNTIFS(C12:C23;G2) ket : C12:C23 - merupakan range yang akan dihitung G2 - merupakan isi dari Shift I
Pada cell H3 diisi rumus :
=COUNTIFS(C12:C23;H2) ket : C12:C23 - merupakan range yang akan dihitung G3 - merupakan ini dari Shift II
Cell I3 merupakan total jumlah karyawan, rumusnya sbb ; =G3+H3 ket : jumlah total dari shift I dan II
Tidak Masuk :Merupakan jumlah karyawan yang tidak masuk yang terbagi dua shift dan dihitung secara otomatis. Pada Cell G4 diisi rumus : =COUNTIFS(C12:C23;G2;D12:D23;"-") ket : C12:C23;G2 artinya Karyawan yang ada di Shift I D12:D23;"-" artinya yang mempunyai jam masuk "-" alias tidak masuk
pada Cell H4 diisi rumus =COUNTIFS(C12:C23;H2;E12:E23;"-") ket : menghitung karyawan yang tidak masuk di Shift II
Masuk Tepat Waktu : Meghitung karyawan yang masuk tepat pada waktunya Cell G5 diisi =COUNTIFS(C12:C23;G2;E12:E23;"Ok") penjelasan : Pada E12:23 dihitung status "Ok"
Cell H5 diisi =COUNTIFS(C12:C23;H2;E12:E23;"Ok")
Cell G6 : =COUNTIFS(C12:C23;G2;E12:E23;"Telat")
Cell H6 : =COUNTIFS(C12:C23;H2;E12:E23;"Telat")
Cell G7 : =COUNTIFS(C12:C23;H2;G12:G23;"Ok")
Cell H7 : =COUNTIFS(C12:C23;H2;G12:G23;"Ok")
Cell G8 : =COUNTIFS(C12:C23;G2;G12:G23;"Pulang Awal")
cell H8 : =COUNTIFS(C12:C23;H2;G12:G23;"Pulang Awal")
Untuk lebih mudahnya dalam mempelajari saya sertakan file excelnya disini DownLoad. Tetapi rumus yang saya berikan hanya bisa jalan di excel office 7. Dengan penjelasan diatas mudah-mudahan dapat dimengerti terutama pada penggunaan fungsi COUNTIFS().
Menghitung Uang Lembur Karyawan Menghitung Uang Lembur Karyawan
Program ini saya bikin dari Excel versi 2007, dan asal usul ide pembuatan program ini berdasarkan dari suatu pertanyaan dari salah seorang rekan pengunjung yang mengirimkan suatu file Excel kepada saya untuk memecahkan kasusnya. Dan saya juga sangat berterima kasih untuk itu, karena sering pertanyaan-pertanyaan yang datang kepada saya mendatangkan suatu ide-ide baru dan trik-trik baru untuk memecahkan berbagai kasus yang mungkin tidak dapat ditemukan di buku mana pun, yang mana saya sendiri pun sering tidak menduga trik-trik yang muncul begitu saja saat saya mendapatkan suatu kasus yang menarik. Jadi sangat sayang kalau saya lupakan begitu saja tanpa ada orang yang bisa memanfaatkannya dengan baik. Dan seperti biasanya kalau ada suatu kasus yang menarik akan saya buatkan suatu artikel agar dapat di pelajari bersama-sama, mudah-mudah dengan begitu ilmu akan selalu terus sambung menyambung dan berkembang kearah yang lebih baik lagi. Program ini terdiri dari 2 (dua) sheet, sesuai dengan file aslinya yang dikirimkan kepada saya, dan formatnya pun tidak terlalu banyak perubahan walau ada perbaikan di sana-sini sesuai dengan kebutuhan teknis. Dan ada beberapa tambahan beberapa kolom yang saya hidden untuk kebutuhan teknis pula, tetapi yang paling terbanyak disini adalah penambahan rumus-rumus, dan maaf rumus-rumus disini mungkin agak sulit sekali untuk di jelaskan dengan kata-kata, jadi saya lebih menekankan bagaimana cara menjalankan program ini. Program ini juga tidak menggunakan program Macro sedikitpun walaupun fasilitas ini sudah disediakan oleh Excel, tetapi saya tidak pernah menggunakannya, maklum dasarnya adalah bahasa Visual Basic yang bukan merupakan spesialisasi saya. Jadi saya hanya menggunakan murni pakai rumus-rumus Excel yang ada. Jadi para penggemar Excel dapat mempelajari rumus-rumus yang saya berikan dengan mempelajari fungsi-fungsi apa saja yang saya gunakan untuk membentuk program ini. Sebenarnya program ini sangat mudah dibuat kalau kita menggunakan khusus bahasa pemrograman database, akan tetapi berhubung kita menggunakan program excel hal ini menjadikannya agak sulit, karena excel sangat terbatas sekali dalam pengelolaan Query. Walaupun ada disediakan fasilitas untuk itu, akan tetapi end user tentunya akan kesulitan untuk memanfaatkannya. Seperti yang telah saya sampaikan sebelumnya, program ini terdiri dari 2 (dua) sheet. Sheet yang pertama berisi Rekap Bulanan dan yang kedua Rekap Harian. Ada hal yang terbalik alias melanggar kaidah-kaidah hukum pemrograman standard disini. Yaitu Rekap Bulanan diisi terlebih dahulu untuk menghasilkan Rekap Harian secara otomatis. Ini sebenarnya sangat melanggar prinsip pemrograman standard tapi justru dengan memakai excel hal ini lebih effisien. Pertama kali dapat kasus begini saya agak bingung juga dan kepikiran terus karena penasaran, tapi Alhamdulillah solusinya dapat dip ecahkan. Penjelasan saya akan saya bagi dua bagian, yang pertama Cara Menggunakan Program dan yang kedua Maintenance. I. Cara Menggunakan Program . Pada Sheet pertama, yaitu Rekap Bulanan ada tabel Tanggal Libur, tanggal ini harus diisi termasuk hari minggu. Pembayaran lembur pada hari Libur atau tanggal merah berbeda dengan tanggal biasa. Dan harus diisi secara urut dan teratur, jangan loncat-loncat seperti misalnya habis tanggal 5 tanggal 3, jadi yang benar tanggal 3 dulu baru tanggal 5. Contohnya saya berikan seperti dibawah ini :
Berikutnya adalah mengisi besarnya Uang Makan dan dan PPH21 seperti yang ditunjukkan gambar dibawah ini:
Apabilah PPH21 tidak diperhitungkan kasih nilai nol saja.
NIK : harus diisi dan merupakan kode karywan dan bersifat unique, artinya kode ini tidak boleh sama dengan kode karyawan lainnya. Nama : merupakan nama karyawan. Tarif Lembur/ Jam Hari Biasa : harus diisi. Tarif Lembur/Jam Hari Libur : terisi otomatis dengan perhitungan 2 (dua) kali Tarif Lembur pada hari biasa.
Pada baris tanggal sudah terisi dari tanggal 1 (satu) sampai dengan tanggal 31 ( tiga satu). Pada baris di bawahnya ada symbol “X” untuk menandakan Hari Libur, dan “H” hari biasa, symbol ini akan terisi secara otomatis pada saat kita sudah mengisi tanggal-tanggal hari libur seperti yang sudah saya terangkan diatas. Pada bagian jumlah jam lembur, Anda di sini harus mengisi, berapa jumlah jam lembur karyawan.
Kolom Hari Biasa : merupakan jumlah total jam lembur hari biasa, dan kolom ini terisi otomatis. Kolom Hari Libur : merupakan jumlah total jam lembur pada hari libur, kolom ini juga terisi otomatis. Kolom Uang Lembur : akan terhitung otomatis. Uang Makan : Kolom ini juga terisi otomatis, dengan syarat jam lembur perharinya harus lebih dari 2 jam baru uang makan dapat diperhitungkan satu kali. Jumlah kotor : kolom ini terisi otomatis, kolom ini merupakan total uang yang diterima karyawan sebelum dipotong pajak (PPH21). Jumlah bersih : Jumlah uang lembur yang harus diterima karyawan sesudah dipotong pajak. Pada tabel bagian atas dari gambar ini, menyatakan perhitungan total uang sesuai dengan kolom masing-masing. Perhitungan totalnya memang tidak saya tempatkan dibawah seperti pada umumnya, ini hanya untuk memudahkan teknis saja. Berikutnya penjelasan untuk Sheet ke 2 (dua), Rekap Harian.
Ini merupakan daftar Absensi Lembur Harian yang terbentuk secara otomatis akibat dari perhitungan-perhitungan dari Sheet yang pertama ( Rekap Bulanan ). Yang perlu diisikan di sini hanya Tanggal dan Jam Mulai Lembur, selebihnya terhitung secara Otomatis. II. Maintenance Technical. Program ini saya buat dengan Office Excel 2007, dan saya simpan dalam format Office Excel 2007 pula, jadi file nya mempunyai ekstensi (.xlsx). Sebagian besar sheet yang terisi penuh dengan rumus-rumus, jadi bagian-bagian tersebut saya protect kecuali bagian-bagian untuk meng entry data. Hal ini untuk menghindari kesalahan-kesalahan yang dilakukan user. Jumlah baris yang saya sediakan disini sebesar 1000 baris, jadi total karyawan yang bisa ditampung sebanyak 1000 personil. Apabila Anda ingin merubah rumus-rumusnya untuk menyesuaikan dengan keadaan dengan Perusahaan Anda, maka maka protectnya harus dibuka terlebih dahulu dengan jalan : buka tab Review || Unprotect Sheet , setelah terbuka permintaan password, isikan dengan “jnet99″ dengan huruf kecil semua. Dan jangan lupa untuk memprotect kembali.
Menghitung Jam Lembur Karyawan Saya pernah menerima kasus seperti ini : Bagaimana caranya menghitung jumlah jam lembur karyawan lewat rumusan Excell. Dimana kalau lewat 15 menit akan digenapkan menjadi 30 menit, tapi kalau kurang menitnya menjadi nol. Misal jumlah jam lembur 1 jam 15 menit atau kurang maka akan dihitung 1 jam saja. Tapi kalau jumlah jam lemburnya 1 jam 16 menit maka dihitung 1 jam 30 menit. Dari utak-atik program Excel maka ditemukan bentuk rumusan tabel dibawah ini :
Kolom Start : menyatakan awal jam lembur Kolom End : Menyatakan jam karyawan pulang lembur. Kolom duration : untuk mengetahui lamanya lembur Kolom Minute : untuk mengetahui menit dari jam. Kolom Adj : untuk penyesuaian menit Result : disini terbagi dua yaitu Hour (jam) dan Minute (menit). Merupakan hasil akhir dari perhitungan. User hanya diperbolehkan mengisi kolom start dan kolom end saja. Sedangkan kolom yang lainnya sudah diisi rumus. Isi rumusnya sebagai berikut Pada Kolom C3 diisi rumus : =B3-C3 Pada Kolom D3 diisi dengan rumus : =Minute(C3) Pada Kolom E3 diisi dengan rumus : =IF(Minute(C3)>15;30;0) Pada Kolom F3 diisi dengan rumus : =Hour(C3) Pada kolom G3 diisi dengan rumus : =E3 Untuk baris selanjutnya kita tinggal mencopy dari isi-isi rumus tersebut, sehingga kita bisa menghitung jumlah jam kerja karyawan pada saat itu dalam waktu yang lebih ringkas apalagi jumlah karyawan sampai seratus keatas. Sampai disini dulu tulisan saya smoga bermanfaat.
Menghitung Selisih Jam (Ms Excel Untuk Pemula) Editor:
[email protected], Tips dan Trik, dibaca: 7557 ka li dipublikasikan: 17 March 2012 23:02:04 Wita, 1 Tahun yang lalu
BSMsite.com - Contoh kasus sederhana kali ini adalah menghitung selisih jam, misalnya saya ingin mendapatkan hasil dari penguran jam akhir dikurangi jam awal. Hal ini akan sangat mudah jika jam awal lebih kecil dari jam akhir, misaln ya mulai start jam 3:00 dan jam akhir 11:00 maka hanya dengan menggunakan rumus pengurangan biasa sudah bisa terlihat hasilnya. Namun permasalahan akan terjadi apabila jam awal lebih besar daripada jam akhir, misalnya jam awal 19:00 dan jam akhir 6:00. Untuk lebih jelasnya mari kita lihat ilustrasi berikut.
Dari contoh diatas untuk nama Oya dapat kita hitung selisih jam yaitu dengan cara menggunakan formula berikut : =C2-B2
Rumus diatas akan berhasil tanpa error, namun apabila waktu selesai lebih besar dari pada waktu mulai seperti Farhan dan Rika, rumus diatas akan menghasilkan error. Untuk itu kita menghilangkan error tersebut maka formulanya harus dirubah menjadi seperti berikut : =C2-B2+IF(B2>C2;1)
Selanjutnya copy formula tersebut pada baris berikutnya, maka error yang sebelumnya timbul akan hilang. Sedikit penjelasan dari formula diatas, setelah melakukan pengurangan antara kolom C dengan B selanjutnya dilakukan pengecekan apakah kolom B nilainya lebih besar dari kolom C, jika benar maka nilainya akan ditambah satu.
Rumus Hitung Waktu di Excel Untuk melakukan perhitungan waktu baik hari, bulan, maupun tahun anda dapat menggunakan fungsi Date dari Microsoft Excel. Fungsi date memiliki 3 buah parameter yaitu year, month dan day. Pada posting kali ini saya akan memberikan tips dan contoh kasus penggunaan fungsi Date menggunakan Microsoft Excel untuk perhitungan waktu yang akan datang. Sebagai contoh kasus adalah menentukan waktu kadaluarsa dari suatu barang berdasarkan tanggal produksi barang tersebut. Misalnya suatu barang diproduksi pada tangga 2 Januari 2009 akan kadaluarsa pada satu tahun yang akan datang yaitu 2 Januari 2010. Kita akan membuat tabel di excel dengan kolom-kolom Nomor, Nama Barang, Tanggal Produksi dan Tanggal Kadaluarsa. Misalkan judul kolom Nomor ada di A1, Nama Barang di B1, Tanggal Produksi di C1 dan Tanggal Kadaluarsa di D1. Kolom Nomor diisi nomor urut, kolom Nama Barang diisi nama barang, kolom Tanggal Produksi diisi tanggal produksi dan kolom Tanggal Kadaluarsa di cell D2 diisi rumus =DATE(YEAR(C2)+1,MONTH(C2),DAY(C2)) Keterangan rumus: Rumus di atas adalah untuk menghitung satu tahun dari tanggal produksi dengan parameter:
YEAR(C2)+1 adalah menghitung 1 tahun dari tanggal produksi. MONTH(C2) adalah menentukan bulan kadaluarsa sesuai dengan bulan produksi. DAY(C2) adalah menentukan hari kadaluarsa sesuai dengan hari produksi.
Jika waktu kadaluarsa bukan 1 tahun melainkan 6 bulan, maka rumus pada kolom Tanggal kadaluarsa diubah menjadi: =DATE(YEAR(C2),MONTH(C2)+6,DAY(C2)). Pada rumus yang baru ini parameter tahun tidak ditambah 1 melainkan parameter bulan yang ditambah 6. Begitu juga jika waktu kadaluarsa ditentukan dalam satuan hari misalnya 200 hari, maka rumus pada kolom tanggal kadaluarsa adalah: =DATE(YEAR(C2),MONTH(C2),DAY(C2)+200)
