Archive

Posts Tagged ‘formula’

formula untuk membandingkan format waktu dalam excel (compare time in excel)

April 25, 2013 32 comments

excel formula

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…..