Formula or VBA for Biometrics attendance

B

Balkhair

New Member
Joined
Oct 24, 2019
Messages
6
Dear Sir/Mam,

Below is an extract raw data from Biometrics attendance machine, I need toseparate the list of staff who's working hours are less than 9hrs, staff who were Absent and staff who were present in different sheets along with the date.

Note: The date format is in text.



Run by: Date:22/10/201921:05
User Name Shift IN- OUT- IN- OUT- 1st 2nd LateEarlyOverAuth Auth WorkMan Reason
ID SPFID SPFID SPFID SPFID Half Half -IN-OUTTimeOTC-OFFHrs Entry
15/09/2019(Sunday)
SnoG0111Mohammad Ripon Hazary GS 05:52 15:22 PR PR 09:30
SnoG014JOHN PAULO VILLACORTA - B1-14 GS 06:58-516:01-6 PR PR 09:03
SnoG018Rovel Meah GS 06:43 17:00 PR PR 10:17
SnoG020Jakir Hazari Matu Miah Hazari GS 12:47 21:01 PR PR 08:14
SnoG021Odunayo Ogunniy GS 05:48 18:00 PR PR 12:12
SnoG033Jally Aboy Libaton GS 06:57 17:02 PR PR 10:05
SnoG040Ratnakaran Malal GS 06:03 20:11 PR PR 14:08
SnoG044Janet Cruz Zacarias GS 07:13 16:42 PR PR 09:29
SnoG055Ruhel Miah GS 12:47 21:19 PR PR 08:32
SnoG058Dennis Tronco GS 07:09 18:11 PR PR 11:02
SnoG062Rodel Dela Tonga GS 07:01 17:50 PR PR 10:49
SnoG070Mansour Hassan Ibrahim Mansour B1-13 GS 07:02 16:10 PR PR 09:08
SnoG076Ravinder Kumar GS 07:36 17:40 PR PR 10:04
SnoG077RAMZI KHALIL- B1-12 GS 07:15 15:42 PR PR 08:27
SnoG079Harilal Bahttarai GS 06:00 16:12 PR PR 10:12
SnoG091Syed Tabran Ahmed GS 07:03 16:50 PR PR 09:47
SnoG092Wathek Talebaoui GS AB AB
SnoG093SAMER DAGHMASH - B1-18 GS 07:16 17:58 PR PR 10:42
SnoG094Suganthaveni Madasamy GS 08:14 16:08 PR PR 07:54
SnoG097Abdul Raheem Kalapurakal Hamza GS AB AB
SnoG107Mohannad Naim Uddin GS 10:36 15:48 PR PR 05:12
SnoG119Mohammad Sabri GS 07:12 16:00 PR PR 08:48
SnoG124Reda El Aqad GS 07:16 16:12 PR PR 08:56
SnoG126IDRIS GAMIL GS 07:38 21:18 PR PR 13:40
SnoG127Imene Mohamed Mrad GS 07:05 17:43 PR PR 10:38
SnoG128Shwaib Mahadik GS 06:41 18:08 PR PR 11:27
SnoG129Oshin Nicola GS 06:57 16:51 PR PR 09:54
SnoG130BELAL NACHAAT GS AB AB
BC003Karam Mohamed Abdrabokasim GS 06:33 18:38 PR PR 12:05
IHD019Abdul Agees Batchayee 07:03 16:03 AB AB 09:00
PBU0045Hatem S A Abutarieh GS 08:42 11:49 PR AB 03:07
PBU0055Ashraf Omer Abd Elrazig Ahmed - B1-55 GS 07:16 15:44 PR PR 08:28
16/09/2019(Monday)
SnoG0111Mohammad Ripon Hazary GS 05:52 15:20 PR PR 09:28
SnoG014JOHN PAULO VILLACORTA - B1-14 GS 07:07-516:36-6 PR PR 09:29
SnoG018Rovel Meah GS 06:15 16:28 PR PR 10:13
SnoG020Jakir Hazari Matu Miah Hazari GS 12:48 21:04 PR PR 08:16
SnoG021Odunayo Ogunniy GS 05:45 18:01 PR PR 12:16
SnoG033Jally Aboy Libaton GS 07:05 16:26 PR PR 09:21
SnoG040Ratnakaran Malal GS 05:21 18:53 PR PR 13:32
SnoG044Janet Cruz Zacarias GS 07:18 18:01 PR PR 10:43
SnoG055Ruhel Miah GS 12:48 21:20 PR PR 08:32
SnoG058Dennis Tronco GS 08:59 18:41 PR PR 09:42
SnoG062Rodel Dela Tonga GS 07:02 19:29 PR PR 12:27
SnoG070Mansour Hassan Ibrahim Mansour B1-13 GS 07:03 17:13 PR PR 10:10
SnoG076Ravinder Kumar GS 07:50 18:51 PR PR 11:01
SnoG077RAMZI KHALIL- B1-12 GS 07:19 17:13 PR PR 09:54
SnoG079Harilal Bahttarai GS 05:56 16:11 PR PR 10:15

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col span="4"><col span="2"><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Dear Experts,

Can someone please help me with this problem.


Balkhair said:
Dear Sir/Mam,

Below is an extract raw data from Biometrics attendance machine, I need toseparate the list of staff who's working hours are less than 9hrs, staff who were Absent and staff who were present in different sheets along with the date.

Note: The date format is in text.



Run by:Date:22/10/201921:05
User Name Shift IN- OUT- IN- OUT- 1st 2nd LateEarlyOverAuthAuthWorkMan Reason
ID SPFID SPFID SPFID SPFID Half Half -IN-OUTTimeOTC-OFFHrs Entry
15/09/2019(Sunday)
SnoG0111Mohammad Ripon Hazary GS05:5215:22 PR PR09:30
SnoG014JOHN PAULO VILLACORTA - B1-14 GS06:58-516:01-6 PR PR09:03
SnoG018Rovel Meah GS06:4317:00 PR PR10:17
SnoG020Jakir Hazari Matu Miah Hazari GS12:4721:01 PR PR08:14
SnoG021Odunayo Ogunniy GS05:4818:00 PR PR12:12
SnoG033Jally Aboy Libaton GS06:5717:02 PR PR10:05
SnoG040Ratnakaran Malal GS06:0320:11 PR PR14:08
SnoG044Janet Cruz Zacarias GS07:1316:42 PR PR09:29
SnoG055Ruhel Miah GS12:4721:19 PR PR08:32
SnoG058Dennis Tronco GS07:0918:11 PR PR11:02
SnoG062Rodel Dela Tonga GS07:0117:50 PR PR10:49
SnoG070Mansour Hassan Ibrahim Mansour B1-13 GS07:0216:10 PR PR09:08
SnoG076Ravinder Kumar GS07:3617:40 PR PR10:04
SnoG077RAMZI KHALIL- B1-12 GS07:1515:42 PR PR08:27
SnoG079Harilal Bahttarai GS06:0016:12 PR PR10:12
SnoG091Syed Tabran Ahmed GS07:0316:50 PR PR09:47
SnoG092Wathek Talebaoui GS AB AB
SnoG093SAMER DAGHMASH - B1-18 GS07:1617:58 PR PR10:42
SnoG094Suganthaveni Madasamy GS08:1416:08 PR PR07:54
SnoG097Abdul Raheem Kalapurakal Hamza GS AB AB
SnoG107Mohannad Naim Uddin GS10:3615:48 PR PR05:12
SnoG119Mohammad Sabri GS07:1216:00 PR PR08:48
SnoG124Reda El Aqad GS07:1616:12 PR PR08:56
SnoG126IDRIS GAMIL GS07:3821:18 PR PR13:40
SnoG127Imene Mohamed Mrad GS07:0517:43 PR PR10:38
SnoG128Shwaib Mahadik GS06:4118:08 PR PR11:27
SnoG129Oshin Nicola GS06:5716:51 PR PR09:54
SnoG130BELAL NACHAAT GS AB AB
BC003Karam Mohamed Abdrabokasim GS06:3318:38 PR PR12:05
IHD019Abdul Agees Batchayee07:0316:03 AB AB09:00
PBU0045Hatem S A Abutarieh GS08:4211:49 PR AB03:07
PBU0055Ashraf Omer Abd Elrazig Ahmed - B1-55 GS07:1615:44 PR PR08:28
16/09/2019(Monday)
SnoG0111Mohammad Ripon Hazary GS05:5215:20 PR PR09:28
SnoG014JOHN PAULO VILLACORTA - B1-14 GS07:07-516:36-6 PR PR09:29
SnoG018Rovel Meah GS06:1516:28 PR PR10:13
SnoG020Jakir Hazari Matu Miah Hazari GS12:4821:04 PR PR08:16
SnoG021Odunayo Ogunniy GS05:4518:01 PR PR12:16
SnoG033Jally Aboy Libaton GS07:0516:26 PR PR09:21
SnoG040Ratnakaran Malal GS05:2118:53 PR PR13:32
SnoG044Janet Cruz Zacarias GS07:1818:01 PR PR10:43
SnoG055Ruhel Miah GS12:4821:20 PR PR08:32
SnoG058Dennis Tronco GS08:5918:41 PR PR09:42
SnoG062Rodel Dela Tonga GS07:0219:29 PR PR12:27
SnoG070Mansour Hassan Ibrahim Mansour B1-13 GS07:0317:13 PR PR10:10
SnoG076Ravinder Kumar GS07:5018:51 PR PR11:01
SnoG077RAMZI KHALIL- B1-12 GS07:1917:13 PR PR09:54
SnoG079Harilal Bahttarai GS05:5616:11 PR PR10:15

<tbody>
</tbody>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
insert row after last duplicated item based on selected combobox on userform
Replies
2
Views
269
Ali M
A
R
  • Question
Random
Replies
1
Views
440
Fluff
Fluff
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
91
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
357
kevin9999
K
H
  • Solved
Counting Theory and Practical Periods with Conditions
Replies
7
Views
257
Fluff
Fluff

Forum statistics

Threads
1,214,591
Messages
6,120,432
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top