Pak IRA, Silahkan di coba langkah berikut, attachment terlampir :
1. Terlebih dahulu ubah format kolom "Date" dan "Time" yang tadi text ke Date
dan Time seperti berikut:
- kolom date : =DATEVALUE(TEXT($A2,"m-d-yy"))
- kolom time : =TIMEVALUE(RIGHT(TEXT($A2,"dd-mmm-yy hh:mm"),5))
2. Pada sheet Resume, silahkan gunakan formula berikut jangan lupa tekan
bersamaan ctrl+shift+enter untuk apply array:
- kolom start: ={IFERROR(MIN(OFFSET('Base data'!$I$1,MATCH(1,('Base
data'!$D$2:$D$118=$E3)*($C3='Base data'!$H$2:$H$118),0),0,COUNTIFS('Base
data'!$D$2:$D$118,"="&resume!E3,'Base data'!$H$2:$H$118,"="&resume!C3),1)),"")}
- kolom finish : ={IFERROR(MAX(OFFSET('Base data'!$I$1,MATCH(1,('Base
data'!$D$2:$D$118=$E12)*($C12='Base data'!$H$2:$H$118),0),0,COUNTIFS('Base
data'!D11:D127,"="&resume!E12,'Base data'!H11:H127,"="&resume!C12),1)),"")}
________________________________
From: Imron Rachmanu Adji <[email protected]>
To: [email protected]
Sent: Monday, October 15, 2012 10:20 AM
Subject: ]] XL-mania [[ meresume absen awal dan akhir, per hari per orang
--- On Mon, 10/15/12, Imron Rachmanu Adji <[email protected]> wrote:
>From: Imron Rachmanu Adji <[email protected]>
>Subject: Resume
>To: [email protected]
>Date: Monday, October 15, 2012, 1:16 AM
>
>
>dear XL-mania
>seperti file terlampir, didalam file itu saya punya data base seperti
>terlampir, formula bagaimana didalam sheet resume kita memperoleh data setiap
>orang "mulai awal start" dan "saat mengakhiri pekerjaan" pada setiap harinya.
>best regards
>IRA
sample.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
