calculate time attendance from text log file

[batupahat]

hi,
im very new to excel formulas. im just a basic user. im getting data from my finger print device in text log files . Each employee will punch-in morning punch-out for lunch, punch-in after lunch and evening punch-out.

help me to calculate monthly working time. The data are

No Mchn EnNo Name DateTime
000001 1 000000001 ERFAN 2013/09/21 14:24
000002 1 000000001 ERFAN 2013/09/21 14:24
000003 1 000000003 Hafsah 2013/11/07 15:39
000004 1 000000002 Megawati 2013/11/07 15:39
000005 1 000000001 ERFAN 2013/11/07 16:34
000006 1 000000009 abdurRahman 2013/11/07 16:40
000007 1 000000003 Hafsah 2013/11/07 16:44
000008 1 000000001 ERFAN 2013/11/07 18:28
000009 1 000000003 Hafsah 2013/11/08 08:32
000010 1 000000002 Megawati 2013/11/08 08:32
000011 1 000000004 Sarimah 2013/11/08 08:32
000012 1 000000009 abdurRahman 2013/11/08 08:36
000013 1 000000008 Rahmanmill 2013/11/08 08:44
000014 1 000000005 Amirul 2013/11/08 08:45
000015 1 000000006 Azhari 2013/11/08 08:45
000016 1 000000002 Megawati 2013/11/08 12:07
000017 1 000000003 Hafsah 2013/11/08 12:07
000018 1 000000004 Sarimah 2013/11/08 12:07
000019 1 000000005 Amirul 2013/11/08 12:10
000020 1 000000006 Azhari 2013/11/08 12:10
000021 1 000000008 Rahmanmill 2013/11/08 12:45
000022 1 000000004 Sarimah 2013/11/08 14:56
000023 1 000000002 Megawati 2013/11/08 14:58
000024 1 000000003 Hafsah 2013/11/08 14:58
000025 1 000000005 Amirul 2013/11/08 14:59
000026 1 000000009 abdurRahman 2013/11/08 14:59
000027 1 000000006 Azhari 2013/11/08 14:59
000028 1 000000008 Rahmanmill 2013/11/08 15:00
000029 1 000000001 ERFAN 2013/11/08 15:01
000030 1 000000004 Sarimah 2013/11/08 17:53
000031 1 000000003 Hafsah 2013/11/08 17:55
000032 1 000000002 Megawati 2013/11/08 17:56
000033 1 000000006 Azhari 2013/11/08 17:59
000034 1 000000005 Amirul 2013/11/08 17:59
000035 1 000000009 abdurRahman 2013/11/08 18:01
000036 1 000000008 Rahmanmill 2013/11/08 18:13
000037 1 000000004 Sarimah 2013/11/09 08:31
000038 1 000000003 Hafsah 2013/11/09 08:32
000039 1 000000002 Megawati 2013/11/09 08:32
000040 1 000000006 Azhari 2013/11/09 08:41
000041 1 000000005 Amirul 2013/11/09 08:41
000042 1 000000009 abdurRahman 2013/11/09 08:42
000043 1 000000008 Rahmanmill 2013/11/09 08:42
000044 1 000000003 Hafsah 2013/11/09 13:07
000045 1 000000002 Megawati 2013/11/09 13:08
000046 1 000000004 Sarimah 2013/11/09 13:44
000047 1 000000005 Amirul 2013/11/09 14:01
000048 1 000000006 Azhari 2013/11/09 14:02
000049 1 000000002 Megawati 2013/11/09 14:05
000050 1 000000003 Hafsah 2013/11/09 14:05
000051 1 000000008 Rahmanmill 2013/11/09 14:09
000052 1 000000004 Sarimah 2013/11/09 14:19
000053 1 000000009 abdurRahman 2013/11/09 15:05
000054 1 000000006 Azhari 2013/11/09 15:07
000055 1 000000008 Rahmanmill 2013/11/09 15:07
000056 1 000000009 abdurRahman 2013/11/09 16:08
000057 1 000000003 Hafsah 2013/11/09 18:09
000058 1 000000004 Sarimah 2013/11/09 18:10
000059 1 000000006 Azhari 2013/11/09 18:11
000060 1 000000005 Amirul 2013/11/09 18:11
000061 1 000000005 Amirul 2013/11/09 18:11
000062 1 000000009 abdurRahman 2013/11/09 18:18
000063 1 000000008 Rahmanmill 2013/11/09 18:28
000064 1 000000001 ERFAN 2013/11/09 18:42

thank you

[rishikrsaw]

Hi,

Pls copy the data and paste (crtl +v) and use text to column (alt d +e ) and go to delimited > and choose space > next > select datetime column> change column format as date > finish....

[rishikrsaw]

if any further assistance required let me know....

thanks
R

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[batupahat]

Thanks Rishikrsaw,

I have tried that already, i can sort all the data but actually i want to calculate how many hours a employee have worked in a month. i need excel to calculate the man hours. so im trying to make a formula to do this. Kindly advice..

Regards,
bp

[rishikrsaw]

Hi

Pls see the attached file sheet #4 (Pivot table).

Pls let me know in case of query....

thanks
R

[batupahat]

Hi,

I went through the excel sheet. Thanks for your help. And i would like to know how are you calculating the total hours for a single Employee. i have highlighted the total hours calculated manually in next sheets(*sheet 3 n 1)
emp1 data.xls


000000003 Hafsah 08/11/2013 November 8:32 Chk in morning
000000003 Hafsah 08/11/2013 November 12:07 Out for Lunch
000000003 Hafsah 08/11/2013 November 14:58 In after Lunch
000000003 Hafsah 08/11/2013 November 17:55 Ckh Out Total Hours
16:15
000000003 Hafsah 09/11/2013 November 8:32 Chk in morning
000000003 Hafsah 09/11/2013 November 13:07 Out for Lunch
000000003 Hafsah 09/11/2013 November 14:05 In after Lunch
000000003 Hafsah 09/11/2013 November 18:09 Ckh Out

Regards,

[Pepe Le Mokko]

How do you differentiate entry and exit times ?

[batupahat]

hi pepo,

its just by looking the time. any one who punch after 12:00 noon is considered as for lunch and usual break time is around 1 hour. we close by 6:00pm . all is jus by looking time.
Tx

[rishikrsaw]

Hi

this is not the data u have given me earlier as now there are login and log off time....

I try to do something pls refer sheet name "solved"....


thanks
R

[batupahat]

HI,

Earlier i sent u unsorted part of the data to just show what i want to do. secondly i thought sometime the data may not enough so i sent another part. ok thank u very much for the support u are giving. how to use this as every month i have to retrieve data from my finger print machine as data log file, then ill import it to excel , sort it out. For eg if i cut and paste all the data will it calculate the wages or how should i use it. advice...

Regards,

[bossmarmar]

hi to all ching chang potatoes and chang chinaso