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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
344
Rhodie72
Rhodie72
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
72
Denny57
D
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
575
Peter_SSs
Peter_SSs
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
F
  • Solved
How to show the nth occurance based on another column
Replies
6
Views
320
feni1388
F

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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