Attendance Record

D

DarkLord22

New Member
Joined
Jul 4, 2017
Messages
4
Hello everybody and thank you for your time.

I have a sofrware that gives me the LASTNAME FIRSTNAME TIME and DATE when someone passes from a checkpoint.

I am trying to create a way that it will short the information by week per person and it will show first time in first time out and total time.
It would be good if it can do the same per month.

I am trying to make it with a pivot table but i cannot make it work.

Any help/guidance is much appreciated.

The information that i get are copied bellow:
[TABLE="width: 406"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 406"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]LASTNAME[/TD]
[TD]FIRSTNAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[/TR]
[TR]
[TD]L1
[/TD]
[TD]F1
[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]7:07:20[/TD]
[/TR]
[TR]
[TD]L2
[/TD]
[TD]F2
[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]7:22:30[/TD]
[/TR]
[TR]
[TD]L3
[/TD]
[TD]F3
[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]8:03:50[/TD]
[/TR]
[TR]
[TD]L3
[/TD]
[TD]F3
[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]8:16:50[/TD]
[/TR]
[TR]
[TD]L4
[/TD]
[TD]F4
[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]8:23:00[/TD]
[/TR]
[TR]
[TD]L4
[/TD]
[TD]F4[/TD]
[TD="align: right"]3/4/2017[/TD]
[TD="align: right"]8:41:20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
This works
Assuming your data is in A2:D7

in E2
=A2&B2
and copy down the column

in F2
=C2+D2
and copy down the column

in G2
=A2&B2

in G3
=IFERROR(INDEX($E$3:$E$7, MATCH(0, COUNTIF($G$2:G2, $E$3:$E$7), 0)),"")
Array formula, use Ctrl-Shift-Enter
and copy down the column

in H2
=IF(G2="","",MIN(IF(G2=E$2:E$7,F$2:F$7)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

in I2
=IF(G2="","",MAX(IF(G2=E$2:E$7,F$2:F$7)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

Earliest time is column H
Latest time is column I

It looks like you want more work done on this but I dont have the time I'm afraid
 
Upvote 0
[TABLE="width: 1027"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]LASTNAME[/TD]
[TD]FIRSTNAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]F1[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]07:07:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]07:22:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:03:50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:16:50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:23:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]L4 in at 8:23, out at 8:41, presumably in again but not registered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:41:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and finally out at 18:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]F1[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]15:20:25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:20:25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]comments ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:25:10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:28:40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]17:10:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]18:05:44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 1027"]
<tbody>[TR]
[TD]LASTNAME[/TD]
[TD]FIRSTNAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]F1[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]07:07:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]07:22:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:03:50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:16:50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:23:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]L4 in at 8:23, out at 8:41, presumably in again but not registered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]08:41:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and finally out at 18:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]F1[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]15:20:25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:20:25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]comments ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:25:10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]F2[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]16:28:40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]F3[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]17:10:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]F4[/TD]
[TD="align: right"]03/04/2017[/TD]
[TD="align: right"]18:05:44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Yes Correct every time someone passes it writes it down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
372
Rhodie72
Rhodie72
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
325
shg
shg
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
98
Denny57
D
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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