Home

How to get the working hours\day

%3CLINGO-SUB%20id%3D%22lingo-sub-462572%22%20slang%3D%22en-US%22%3EHow%20to%20get%20the%20working%20hours%5Cday%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462572%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3EWe%20are%20using%20fingerprint%20device%20to%20record%20staff%20attending%20and%20leaving%20time.%26nbsp%3Bwhen%20we%20extract%20the%20monthly%20report%20from%20the%20device%2C%20we%20need%20to%20manipulate%20this%20report%20to%20serve%20our%20needs.%20The%20extracted%20reports%20contain%20four%20columns%20(Emp_ID%2CEmp_name%2C%20Date%2C%20time)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20device%20saves%20a%20record%20every%20time%20the%20employee%20put%20his%2Fher%20fingerprint(either%20for%20time%20in%20or%20time%20out)%2C%20so%20for%20every%20working%20day%20the%20employee%20must%20have%20two%20records%20(%20we%20have%20a%20problem%2C%20some%20staff%20have%20just%20one%20record%20(see%203)%26nbsp%3B%20we%20do%20not%20know%20if%20it%20is%20for%20time%20in%20or%20out%2C%20some%20have%20double%20records%20for%20time%20in(see%2012%2C13)%2C%20some%20seems%20like%20the%20staff%20record%20the%20time%20out%20then%20he%20stayed%20more%20time%20then%20he%20record%20another%20time%20out%20(see%2017%2C18%2C19)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20staff%20come%20and%20leave%20in%20different%20times%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20need%20to%20calculate%20the%20working%20hours%20per%20day%20for%20every%20employee%20with%20taking%20the%20consideration%20the%20points%20mentioned%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EEmp_ID%3C%2FTD%3E%3CTD%3EEmp_Name%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3ETime%3C%2FTD%3E%3CTD%3Eworking%20hours%2FDay%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EAbdullah%26nbsp%3B%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E12%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EAbdullah%26nbsp%3B%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A06%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EKhaled%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E11%3A09%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EAhmed%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E09%3A58%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EAhmed%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A52%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EAkram%26nbsp%3B%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E12%3A29%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EAkram%26nbsp%3B%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A44%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3ESaeed%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E08%3A07%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3ESaeed%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E13%3A33%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EMona%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E10%3A55%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EMona%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A36%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EHashem%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E11%3A08%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EHashem%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E11%3A08%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EHashem%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E19%3A06%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ERana%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E09%3A41%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ERana%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E13%3A17%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EEbrahim%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E09%3A27%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EEbrahim%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A24%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E19%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EEbrahim%3C%2FTD%3E%3CTD%3E02%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A36%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EAbdullah%26nbsp%3B%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E11%3A44%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E21%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EAbdullah%26nbsp%3B%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A35%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E22%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EAhmed%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E09%3A53%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EAhmed%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A56%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EAkram%26nbsp%3B%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E12%3A18%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EAkram%26nbsp%3B%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A43%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3ESaeed%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E08%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3ESaeed%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E13%3A11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EMona%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E13%3A32%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EMona%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E18%3A35%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EHashem%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A54%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ERana%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E08%3A15%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ERana%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E13%3A27%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EEbrahim%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E08%3A26%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EEbrahim%3C%2FTD%3E%3CTD%3E03%2F02%2F2019%3C%2FTD%3E%3CTD%3E14%3A29%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-462572%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Jihad Al-Jarady
Occasional Contributor

Hi guys,

We are using fingerprint device to record staff attending and leaving time. when we extract the monthly report from the device, we need to manipulate this report to serve our needs. The extracted reports contain four columns (Emp_ID,Emp_name, Date, time),

 

The device saves a record every time the employee put his/her fingerprint(either for time in or time out), so for every working day the employee must have two records ( we have a problem, some staff have just one record (see 3)  we do not know if it is for time in or out, some have double records for time in(see 12,13), some seems like the staff record the time out then he stayed more time then he record another time out (see 17,18,19)

 

* staff come and leave in different times


I need to calculate the working hours per day for every employee with taking the consideration the points mentioned above.

 

 Emp_IDEmp_NameDateTimeworking hours/Day
11Abdullah 02/02/201912:00 
21Abdullah 02/02/201918:06 
32Khaled02/02/201911:09 
43Ahmed02/02/201909:58 
53Ahmed02/02/201914:52 
64Akram 02/02/201912:29 
74Akram 02/02/201918:44 
85Saeed02/02/201908:07 
95Saeed02/02/201913:33 
106Mona02/02/201910:55 
116Mona02/02/201918:36 
127Hashem02/02/201911:08 
137Hashem02/02/201911:08 
147Hashem02/02/201919:06 
158Rana02/02/201909:41 
168Rana02/02/201913:17 
1710Ebrahim02/02/201909:27 
1810Ebrahim02/02/201914:24 
1910Ebrahim02/02/201914:36 
201Abdullah 03/02/201911:44 
211Abdullah 03/02/201918:35 
223Ahmed03/02/201909:53 
233Ahmed03/02/201914:56 
244Akram 03/02/201912:18 
254Akram 03/02/201918:43 
265Saeed03/02/201908:00 
275Saeed03/02/201913:11 
286Mona03/02/201913:32 
296Mona03/02/201918:35 
307Hashem03/02/201914:54 
318Rana03/02/201908:15 
328Rana03/02/201913:27 
3310Ebrahim03/02/201908:26 
3410Ebrahim03/02/201914:29 
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies