kali ini saya akan coba membahas sedikit mengenai fungsi pada excel, khususnya untuk membandingkan format waktu (date/time). saya tertarik menulis masalah ini karena saat ini saya sedang mengerjakan rekap absen, dan iseng-iseng saya ingin mencoba membuat formula untuk mengecek keterlambatan setiap pegawai dengan data dari mesin absen dengan menggunakan fingerprint.
awalnya saya harus memeriksa apakah status pointnya adalah kedatangan (check in) atau kepulangan (check out), lalu baru membandingkan dengan batas waktu kedatangan atau kepulangan, dan menentukan flag untuk terlambat atau pulang cepat.
baiklah, untuk mengecek kondisi status point bisa dilihat dari salah satu kolom yang tersedia apakah terisi "Check In" yang berarti kedatangan, atau berisi "Check Out" yang berarti kepulangan. rasanya formula untuk permasalahan ini cukup sederhana yaitu dengan menggunakan fungsi IF yang bentuk dasarnya adalah IF(KONDISI; VALUE/FUNGSI BILA BENAR; VALUE/FUNGSI BILA SALAH). contoh penggunaannya adalah sebagai berikut:
=IF(F15="Check In";"fungsi kedatangan"; "selain fungsi kedatangan")
nah sekarang kita beralih ke permasalahan utama, yaitu membandingkan waktu. pertama yang harus kita lakukan adalah dengan mengkonversi teks dari cell tertentu menjadi format waktu (time), cell ini memiliki teks misalnya "07:20" tanpa tanda petik. teks ini (misalnya pada cell D5) bisa kita konversikan menjadi format waktu dengan fungsi TIME(JAM; MENIT; DETIK). fungsi ini dikombinasikan dengan fungsi LEFT(CELL; JUMLAH DIGIT) dan RIGHT(CELL; JUMLAH DIGIT) untuk mendapatkan angka jam dan menit dari suatu cell tertentu. gabungan dari fungsi ini kita gunakan sebagai berikut:
=TIME(LEFT(D15;2);RIGHT(D15;2);0) -> yang misalnya kita simpan di cell E15
yang pada contoh ini 07:20 akan dikonversi menjadi 07:20 AM dengan format waktu. bila konversi telah selesai kita lakukan maka kita bisa langsung membandingkan dengan waktu yang kita inginkan misalnya jam 07:30, dengan formula kondisi WAKTU1 > WAKTU2. cara menuliskannya adalah sebagai berikut:
=IF(E15>TIME(7;30;0);"status terlambat";"status tidak terlambat")
atau untuk memeriksa apakah pegawai tersebut pulang cepat (kurang dari jam 16:00), maka formulanya kita modifikasi menjadi:
=IF(E15<TIME(16;0;0);"status pulang cepat";"status tidak pulang cepat")
lalu bagaimana lagi? sebenarnya semua fungsi utamanya sudah kita buat,dan bila memang ingin terlihat lebih simpel dengan tidak membuat begitu banyak kolom tambahan, maka fungsinya bisa kita gabungkan agar menjadi seperti ini:
=IF(F21="Check In";(IF((TIME(LEFT(D21;2);RIGHT(D21;2);0))>TIME(7;30;0);"status terlambat";"status tidak terlambat")); (IF((TIME(LEFT(D21;2);RIGHT(D21;2);0))<TIME(16;0;0);"status pulang cepat";"status tidak pulang cepat")))
bila dengan menggunakan formula diatas masih terdapat error, mungkin karena pengaturan separator untuk formula pada excel berbeda dengan yang saya gunakan, coba ganti karakter titik koma ";" menjadi koma ",". selamat mencoba.....
Selamat pagi, saya sedang mengerjakan rekap absensi, sama seperti anda, tapi permasalahannya saya ingin mendapatkan status kurang disipliin apabila datang lewat dari jam 8, tidak disiplin apabila waktu kerja kurang dari 9Jam dan disiplin apabila waktu kerja >= 9 jam... nah saya mencoba menggikuti dari blog anda ini tetapi terdapat masalah pada format waktu cek in krn formatnya " 03/11/2014 14:17:00" saya coba select semua ganti format ttp saja seperti itu..... tapi kalo saya ganti dengan text "17:00" itu baru bisa... mohon solusinya.. atau kalo ada email saya ingin mengirim file excel saya.. thanks
ReplyDeletekalau saya lihat di formatnya ini " 03/11/2014 14:17:00", apa benar selalu ada spasi " " di depan tanggal ini? (spasi di depaan angka "03")
ReplyDeletebila memang ada, mungkin bisa di trim dulu. caranya bisa dilihat di artikel saya yang lain (http://quin2.wordpress.com/2013/02/13/menghilangkan-spasi-trim-whitespace-pada-cell-excel/) nanti setelah spasi sebelah kiri itu bersih semua menjadi "03/11/2014 14:17:00", barulah bisa dimasukkan ke dalam fungsi date, namun tentunya dengan sedikit modifikasi. misalnya seperti ini, untuk membandingkan waktu real kedatangan dengan batas waktu katakanlah jam 08:00:00, asumsi waktu kehadiran disimpan di cell D9:
=IF(TIME(HOUR(D9),MINUTE(D9),SECOND(D9))<TIME(8,0,0),"disiplin","kurang disiplin")
untuk kondisi lebih dari satu, misalnya bila datang sebelum jam 8 dan waktu kerja lebih dari 9 jam maka status "disiplin", maka kodenya kurang lebih seperti ini:
*formula untuk kondisi lebih dari 1 bisa menggunakan AND ataupun OR di dalam fungsi IF tergantung kebutuhan, pada contoh ini lebihcocok menggunakan AND
*waktu kedatangan disimpan pada cell D9
*waktu pulang disimpan pada cell D10
*selisih jam pulang dengan kedatangan disimpan pada cell D11 dengan formula:
=TEXT(D10-D9,"h")
formula status disiplin:
=IF(AND(TIME(HOUR(D9),MINUTE(D9),SECOND(D9))=8),"disiplin","tidak disiplin")
arti formula di atas:
bila (waktu(D9)=8) maka status="disiplin", selain itu maka status="tidak disiplin"
di mana D11 = D10-D9
semoga bisa membantu :)
mau nanya nih mas, jika kondisi ke-2 nya adalah pengurangan sel dari sel IF ke sel tertentu gmn formulanya? jadi begini kondisinya.....kalo seorang pegawai masuk setelah jam 7.30 maka akan di hitung selisih antara jam masuk dan waktu masuk (7.30) tetapi kalo sebaliknya jam masuk kurang dari jam 7.30 maka akan di anggap 0. dan nanti akhir bulan akan di jumlahkan selisih antara jam masuk dan waktu masuknya.
ReplyDeleteterima kasih
coba saya jawab,
ReplyDeletekalo saya ga salah tangkep ini maksudnya bila jam masuk (D9) kurang dari 7.30 pagi maka waktu keterlambatan (D10) akan diisi 0,
namun bila jam masuk (D9) lewat dari 7.30 pagi, maka waktu keterlambatan (D10) akan diisi dengan selisih jam datang (D9) dikurangi jam masuk (7.30 pagi)
begitu?
ok, singkatnya kira2 formulanya seperti ini:
=IF(TIME(HOUR(D9),MINUTE(D9),SECOND(D9))<TIME(7,30,0),0,TEXT(TIME(HOUR(D9),MINUTE(D9),SECOND(D9))-TIME(7,30,0),"h:m:s"))
formula diatas akan saya bagi dulu supaya jadi lebih mudah dipahami:
TIME(HOUR(D9),MINUTE(D9),SECOND(D9))<TIME(7,30,0)
-- saya simbolkan jadi X
TEXT(TIME(HOUR(D9),MINUTE(D9),SECOND(D9))-TIME(7,30,0),"h:m:s")
-- saya simbolkan jadi Z
kurang lebih jadi seperti ini:
=IF(X,0,Z)
-- kalau kondisi X terpenuhi maka nilai cell tersebut adalah 0, bila kondisi X tidak terpenuhi maka nilai cell tersebut adalah Z
lalu kita breakdown lagi penjelasannya, kondisi X adalah:
TIME(HOUR(D9),MINUTE(D9),SECOND(D9))<TIME(7,30,0)
-- membandingkan waktu (jam menit detik dari cell D9 apakah lebih kecil dari jam menit detik 07:30:00)
pada statement Z artinya adalah:
TIME(HOUR(D9),MINUTE(D9),SECOND(D9))-TIME(7,30,0)
-- saya simbolkan dengan A, merupakan pengurangan cell D9 dengan format waktu 7:30:00, jadi:
TEXT(A,"h:m:s")
-- adalah hasil pengurawan waktu pada cell D9 dengan jam 7:30:00, dan ditulis dengan format "h:m:s" yaitu "jam:menit:detik"
nanti cell D10 ini (yang berisi selisih waktu keterlambatan), tinggal dijumlah sesuai kebutuhan
semoga saya ga salah tangkep permasalahannya dan semoga penjelasannya bisa gampang dimengerti... :D