rumus menentukan jam masuk dan pulang dari data dobel

  1. 4 bulan yang lalu

    triya

    Mar 2 jakarta pusat + 37 Poin

    selamat siang mas/mbak sekalian, perkenalkan, saya triya, mohon bantuannya untuk memecahkan masalah saya, saya mengolah data absen, tetapi format datanya digabung tanggal dan waktu di dalam satu cell. waktu pun tidak dibedakan pergi dan pulangnya, dan waktu tersebut dobel atau bahkan tiga kali. ketentuannya, waktu paling awal diakui sebagai jam datang dan waktu paling sore diakui sebagai jam pulang. apakah mas/mbak sekalian punya ide bagaimana cara mengolahnya supaya saya bisa menghitung jumlah keterlambatan? terima kasih sebelumnya, mohon bantuannya :D file terlampir

    Silahkan mbak @Triya pelajari contoh solusi yang mungkin dapat diterapkan dari file terlampir. Pada file terlampir, kolom G (DATE) dan kolom H (TIME) merupakan hasil ekstraksi nilai dari kolom D (Date/Time). Sedangkan untuk kolom K (IN) dan kolom M (OUT) masing-masing merupakan hasil ekstraksi baris data pada kolom H untuk mendapatkan jam paling awal (jam masuk) dan jam paling akhir (jam pulang) pada tanggal yang sama (data yang ditampilkan hanyalah untuk tanggal pertama dari kelompok tanggal yang sama saja).

    Sedangkan kolom L (VALID) merupakan kolom hasil pengujian apakah nilai jam masuk kolom K tepat untuk dijadikan jam masuk, oleh karena ada baris nilai jam masuk yang hampir sama dengan jam pulang. Gunakan nilai pada kolom L ini untuk menghitung jam keterlambatan masuk, contohnya pada kolom O (LATE IN). Pada kolom O (LATE IN), formulasinya menggunakan nilai 0,3125 (7,5/24 = 7:30) sebagai acuan jam masuk, sehingga jika nilai pada kolom L (VALID) melebihi nilai tersebut, maka akan dihitung nilai keterlambatannya.

    Untuk kasus dimana tidak terdapat nilai jam masuk yang sesuai (sesuai hasil validasi pada kolom L), solusinya isi secara manual nilai jam masuk langsung pada kolom H (TIME), jadi overwrite saja nilai pada baris terkait pada kolom tersebut. Contohnya pada sel H19, isi saja langsung nilainya misalkan dengan nilai 07:30. Jika tidak ada masalah, insyaAllah sel L19 akan menghasilkan nilai jam masuk yang valid.

    Untuk contoh ringkasan hasil perhitungan jumlah jam keterlambatan, lihat pada sheet SUMMARY. Nilai pada sheet SUMMARY kolom C (TOTAL) diakumulasi dari kolom O (LATE IN) sheet DATA untuk setiap ID yang sama dengan kolom B (ID) pada sheet SUMMARY.

    Demikian contoh solusi yang dapat saya berikan. Semoga sesuai... ;)

  2. Caton

    Mar 2 Terverifikasi Jawaban Terpilih + 9.023 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    Silahkan mbak @Triya pelajari contoh solusi yang mungkin dapat diterapkan dari file terlampir. Pada file terlampir, kolom G (DATE) dan kolom H (TIME) merupakan hasil ekstraksi nilai dari kolom D (Date/Time). Sedangkan untuk kolom K (IN) dan kolom M (OUT) masing-masing merupakan hasil ekstraksi baris data pada kolom H untuk mendapatkan jam paling awal (jam masuk) dan jam paling akhir (jam pulang) pada tanggal yang sama (data yang ditampilkan hanyalah untuk tanggal pertama dari kelompok tanggal yang sama saja).

    Sedangkan kolom L (VALID) merupakan kolom hasil pengujian apakah nilai jam masuk kolom K tepat untuk dijadikan jam masuk, oleh karena ada baris nilai jam masuk yang hampir sama dengan jam pulang. Gunakan nilai pada kolom L ini untuk menghitung jam keterlambatan masuk, contohnya pada kolom O (LATE IN). Pada kolom O (LATE IN), formulasinya menggunakan nilai 0,3125 (7,5/24 = 7:30) sebagai acuan jam masuk, sehingga jika nilai pada kolom L (VALID) melebihi nilai tersebut, maka akan dihitung nilai keterlambatannya.

    Untuk kasus dimana tidak terdapat nilai jam masuk yang sesuai (sesuai hasil validasi pada kolom L), solusinya isi secara manual nilai jam masuk langsung pada kolom H (TIME), jadi overwrite saja nilai pada baris terkait pada kolom tersebut. Contohnya pada sel H19, isi saja langsung nilainya misalkan dengan nilai 07:30. Jika tidak ada masalah, insyaAllah sel L19 akan menghasilkan nilai jam masuk yang valid.

    Untuk contoh ringkasan hasil perhitungan jumlah jam keterlambatan, lihat pada sheet SUMMARY. Nilai pada sheet SUMMARY kolom C (TOTAL) diakumulasi dari kolom O (LATE IN) sheet DATA untuk setiap ID yang sama dengan kolom B (ID) pada sheet SUMMARY.

    Demikian contoh solusi yang dapat saya berikan. Semoga sesuai... ;)

  3. triya

    Mar 2 jakarta pusat + 37 Poin

    Jazakallah mas, sangat membantu, terimakasih banyak. berarti untuk menghitung waktu pulang sebelum waktunya juga dengan cara yang +-sama seperti menghitung lama terlambat ya mas?

  4. Caton

    Mar 2 Terverifikasi + 9.023 Poin

    Wa jazakillahu khairan.

    Untuk menghitung jam pulang cepat, bisa dibilang konsepnya hampir sama. Intinya buat dahulu kolom validasi untuk jam pulang (misalkan pada kolom N), karena ada kemungkinan data jam pulang tidak tepat (lihat contohnya data yang tidak tepat pada sel M11 atau M35). Formulanya dapat berupa:

    =IF(LEN(M2);IF(M2>13/24;M2;"—");"")

    Notasi 13/24 maknanya jika absensi pulang lebih besar dari jam 13:00. Nah, dari kolom validasi untuk jam pulang tersebutlah data sebenarnya dikalkulasikan. Konsepnya hampir sama dengan jam masuk, dimana untuk jam pulang cepat kondisinya bisa berupa Jam Masuk Istirahat Siang > Jam Absensi Pulang < Jam Pulang Standar. Jadi semisalnya Jam Masuk Istirahat Siang adalah 13:00 dan Jam Pulang Standar adalah 16:00, maka jika Jam Absensi Pulang berada pada range waktu tersebut, nilai jam absensi pulang akan dihitung. Formulanya lebih kurang (misalkan pada sel O2):

    =IF(LEN(N2)*ISNUMBER(N2);IF((M2>13/24)*(M2<16/24);M2-(16/24);0);0)

    Demikian. Silahkan berkreasi dengan formulanya... ;)

  5. 3 bulan yang lalu

    triya

    Mar 4 jakarta pusat + 37 Poin

    siap, terima kasih banyak mas :)

 

atau Mendaftar untuk ikut berdiskusi!