Mengolah waktu masuk keluar dari file excel

  1. 8 bulan yang lalu

    Salam kenal master-master...
    Mau tanya nih, di file "absensi 28 maret 2018" ada field waktu awal dan waktu akhir. gimana caranya supaya dapat diisi langsung diambil dari file "log mesin tgl 27-29 maret" ??
    Dengan ketentuan:

    • fokus pada tanggal 28 dan 29, dihitung 2 hari karena jam kerja dari jam 8 sampai jam 8 besok hari.
    • Mesin 3 dan 1 sebagai masuk, dan Mesin 2 sebagai keluar.
    • 1 hari hanya dapat masuk 1x, hanya punya 1 waktu keluar dan 1 waktu masuk.
    • Jika di masing2 nama tidak ada tercatat di mesin 2 atau mesin 1/3, itu dikosongin saja waktu keluar atau waktu masuknya.

    Mohon Bantuannya master, terima kasih...
    Penjelasan Log.jpg

  2. SAS

    29 Mar 2018 Jakarta Timur + 128 Poin

    @mangazatria
    Untuk mengeluarkan Output yang dimaksud, ada 2 trick yaitu menggunakan formula Vlookup dan Pivot Table.
    Untuk bisa menggunakan Vlookup, kita buat variabel unik yang mengandung nama,no staff, tanggal-KodeMasukkeluar. Termasuk menambah satu kolom Kode_Masuk_Keluar untuk menterjemahkan Keluar atau Masuk sesuai no mesin.
    Silahkan dipelajari lebih lanjut, semoga bisa membantu. Monggo teman-teman lain memberikan masukan.

    Salam,
    SAS

  3. Caton

    29 Mar 2018 Terverifikasi Indonesia + 12.493 Poin

    @SAS...
    Izin ikut menambahkan... :)

    @mangazatria ...
    Terlampir solusi saya dengan cara berbeda dari solusinya mas @SAS di atas, namun dengan tujuan yang sama. Silahkan diskusikan kembali jika ada yang tidak sesuai, baik solusinya mas @SAS maupun solusi dari saya.

    Demikian.

  4. 7 bulan yang lalu

    Terima kasih byk master @SAS @Caton , saya coba dulu... :)

  5. kok tidak bisa dicopy rumus nya om?? saya copy rumus nya dan sesuai letak sel nya, hasilnya gk ada.

  6. Caton

    3 Apr 2018 Terverifikasi Indonesia + 12.493 Poin

    @mangazatria ...

    ... kok tidak bisa dicopy rumus nya om?? saya copy rumus nya dan sesuai letak sel nya, hasilnya gk ada ...

    :D Punya mas @SAS atau punya saya... ?

  7. punya om @Caton

  8. Caton

    3 Apr 2018 Terverifikasi Indonesia + 12.493 Poin
    Di sunting 7 bulan yang lalu oleh Caton

    @mangazatria ...

    Memang formulanya dicopy kemana? Hasilnya gak ada (kosong/Empty Cell/Null Value) bisa disebabkan banyak hal. Namun jika ditinjau dari sisi formula, saya kira ada 2 penyebabnya:

    [1]. Jika nilai pada sel J1 tidak sama dengan 0 (nol) atau 1, maka hasilnya akan sama dengan Null Value. Sel J1 tujuannya sebagai penanda (Flag) apakah kalkulasi absensi berdasarkan tanggal pada sel H1 akan melewati batas 1 hari (contoh tanggal 28/03/2018).

    [2]. Referensi ke data absensi yang tidak tepat. Perhatikan, formula pada kolom E sheet PROCESSING memiliki rujukan (referensi) nilai ke sheet LOG. Apabila terjadi perubahan range rujukannya, dan range yang dituju memiliki nilai yang tidak sesuai (misalnya bukan bertipe numerik), atau range yang dijadikan komparasi berbeda (misalkan kolom C pada sheet PROCESSING yang harusnya dikomparasi dengan kolom B pada sheet LOG, pada saat dicopy merujuk ke kolom yang berbeda) dan lain sebagainya. Untuk masalah ini, pelajari dahulu formula yang saya susun, khususnya range-range rujukannya sebelum menyalin formula tersebut.

    Kalau tidak juga menemukan dimana letak masalahnya, coba lampirkan file yang sedang dikerjakan. Atau bisa juga dengan mencoba solusi dari mas @SAS, mungkin saja lebih tepat.

    Demikian.

  9. Coba lihat dulu om @Caton benar apa tidak.

  10. Caton

    4 Apr 2018 Terverifikasi Indonesia + 12.493 Poin

    @mangazatria ...

    Kesalahan atau sumber masalahnya:

    [1]. Pada sel F3, rujukan rangenya ada yang salah:

    (LOG!$B$3:$B$796=D3)*((LOG!$D$3:$D$796=1)+(LOG!$D$3:$D$796=3))*(LOG!$E$3:$E$796=LOG!$I$2)*(ROW(LOG!$A$3:$A$796)-1)

    Perhatikan, apabila beberapa sel digabungkan (merge), maka alamat dari range yang sel-sel tersebut adalah sel kolom dan baris pertama dari sel yang digabungkan tersebut. Maka notasi LOG!$I$2 seharusnya adalah LOG!$I$1.

    [2]. Pada sel G3, rujukan rangenya ada yang salah (kurang):

    (LOG!$B$3:$B$796=D3)*(LOG!$D$3:$D$796=2)*((LOG!$E$3:$E$796=LOG!$I$1)+((LOG!$E$2:$E$796>LOG!$I$1)*LOG!$K$1))*(ROW(LOG!$A$3:$A$796)-1)

    Seharusnya notasi referensi range (LOG!$E$2:$E$796>LOG!$I$1) adalah (LOG!$E$3:$E$796>LOG!$I$1) .

    [3]. Kita tidak bisa membandingkan nilai bertipe teks dengan nilai bertipe numerik. Contohnya, kolom D pada sheet LOG semua tipe nilainya masih bertipe teks (untuk mengujinya, coba gunakan formula =ISNUMBER(D3)), sehingga formula:

    ((LOG!$D$3:$D$796=1)+(LOG!$D$3:$D$796=3))

    akan menghasilkan FALSE atau 0 (nol), meskipun secara teori beberapa baris data pada kolom D sheet LOG memenuhi kondisi tersebut. Solusinya bisa dengan mengubah format sel pada kolom D sheet LOG tersebut menjadi bertipe numerik, atau dengan mengubah notasi pada formula:

    ((LOG!$D$3:$D$796=1)+(LOG!$D$3:$D$796=3))

    menjadi:

    ((LOG!$D$3:$D$796*1=1)+(LOG!$D$3:$D$796*1=3))

    Untuk yang lainnya coba diperiksa kembali sendiri, mungkin ada yang terlewatkan. Terlampir hasil yang sempat saya periksa.

    Demikian.

  11. Oke om sudah.
    Setelah saya coba formula dari om @Caton ada beberapa yg tidak tampil om (salah), kira2 kenapa ya om??
    Berikut saya lampirkan sheet Backup dan Maintenance yg pakai formula dan Backup (manual) dengan cara manual.

  12. Caton

    5 Apr 2018 Terverifikasi Indonesia + 12.493 Poin

    @mangazatria ...

    Begini saja, saya coba buatkan dengan cara yang sama, namun tampilan dan pendekatan berbeda. Dalam file terlampir, proses kalkulasi langsung dilakukan pada sheet LOG, dengan tujuan proses kalkulasi cukup dilakukan hanya pada satu sheet saja sehingga lebih meringankan proses dan juga akan memudahkan proses penelusuran formula atau hasil kalkulasi.

    Sheet lainnya hanya akan mengambil data pada tabel bantu kalkulasi pada sheet LOG berdasarkan kunci NO. STAFF. Hal-hal yang perlu diperhatikan:

    — Pada sheet LOG, nilai pada kolom B, kolom D, kolom E dan kolom F harus bertipe numerik. Jadi apabila akan menambahkan data (misalkan setelah baris ke-800), pastikan nilai-nilai pada kolom tersebut bertipe numerik. Atau bisa juga dengan mengubah formulanya dengan menambahkan operator perkalian dengan 1 sebagaimana yang telah saya jelaskan sebelumnya.

    — Pada file terlampir, saya menggunakan Dynamic Named Range, dimana nama range utamanya adalah DATA.ABSENSI dan ABSENSI.FILTER. Tujuannya agar saat terjadi penambahan data absensi pada kolom A sampai dengan kolom F, tabel Helper (kolom H sampai dengan kolom S) akan menyesuaikan range kalkulasi secara otomatis. Dan pastikan juga, saat dilakukan penambahan data pada kolom yang saya sebutkan tadi, baris tabel Helper juga disesuaikan dengan baris data yang telah ditambahkan. FYI, pada dasarnya saya lebih menyukai penggunaan Tabel Object untuk mengotomasi proses-proses seperti ini.

    — Periksa kembali hasil kalkulasi pada tabel Helper sheet LOG. Perlu dipahami, saya hanya menyusun formulanya saja, dan tidak melakukan pemeriksaan secara menyeluruh apakah hasilnya sudah tepat atau belum. Mungkin saja ada kasus dengan nilai data tertentu yang tidak tepat formulasinya.

    Demikian, semoga bermanfaat.

  13. makasi banyak om @Caton , saya coba eksekusi dulu...

  14. Om @Caton mau nanya,
    kan ini rumus lembur
    I
    =IF($O7=1,CHOOSE($P7,IF(AND($N7>10/24,$N7<17/24),ROUND(($N7-9/24)*24,0),""),IF(AND($N7>17/24,$N7<1),ROUND(($N7-17/24)*24,0),"")),"")
    II
    =IF($O7=2,CHOOSE($P7,IF(AND($N7>9/24,$N7<15/24),ROUND(($N7-8/24)*24,0),""),IF(AND($N7>15/24,$N7<1),ROUND(($N7-15/24)*24,0),"")),"")
    III
    =IF($O7=3,CHOOSE($P7,IF(AND($N7>8/24,$N7<16/24),ROUND(($N7-7/24)*24-($L7>$M7),0),""),IF(AND($N7>16/24,$N7<1),ROUND(($N7-16/24)*24,0),"")),"")

    Ternyata cuma ada lembur II dan III.
    Lembur I pindah ke lembur II, Lembur II pindah ke III, nah Lembur III ke Lembur II (Bukan Lembur I). Lembur II jadi digabung, saya gabungkan rumus nya kok tdk bisa ya om? gmn buat supaya kalo tidak ada waktu lemburnya jadi 0 bukan kosong sel nya.
    Makasi om...

  15. Caton

    10 Apr 2018 Terverifikasi Indonesia + 12.493 Poin

    @mangazatria ...

    Coba diperiksa kembali file terlampir. Semoga sesuai.

    Demikian.

  16. om @Caton , I dan III nya sebelumnya digabungi ke 2 dan II nya sblumnya jadi ke 3. gitu kira2 .Capture.JPG

    kalo ini masuk ke III om.
    Makasi om

  17. Caton

    10 Apr 2018 Terverifikasi Indonesia + 12.493 Poin

    @mangazatria ...

    Coba diperiksa kembali file terlampir. Semoga sesuai.

    Demikian.

  18. om @Caton sprtinya ada yg salah ,
    sebelum.JPG
    ini sebelum

    rev 2.JPG
    ini sesudah.

    ketentuannya kmarin,
    shift I 08-17 (9 jam),
    shift II 17-01 (8 jam),
    Shift III 01-08 (7 jam).
    diluar jam itu kena lembur om,
    makasi sblumnya om...

  19. Caton

    11 Apr 2018 Terverifikasi Indonesia + 12.493 Poin
    Di sunting 7 bulan yang lalu oleh Caton

    @mangazatria ...

    Coba modifikasi sedikit formula untuk kolom Lembur II (kolom Q), tambahkan notasi $O7=1 dan $O7=3. Kemudian salin formula pada sel Q7 tersebut ke bawah. Letak modifikasinya lebih kurang seperti gambar berikut:

    Untitled.png

    Dan mohon dipelajari formula dengan seksama agar paham apa yang bisa dimodifikasi apabila terdapat kesalahan atau hasil yang tidak tepat.

    Demikian.

  20. Terima kasih om @Caton

 

atau Mendaftar untuk ikut berdiskusi!