Attendance Record

DarkLord22 · Jul 4, 2017

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:

LASTNAME	FIRSTNAME	DATE	TIME
L1	F1	3/4/2017	7:07:20
L2	F2	3/4/2017	7:22:30
L3	F3	3/4/2017	8:03:50
L3	F3	3/4/2017	8:16:50
L4	F4	3/4/2017	8:23:00
L4	F4	3/4/2017	8:41:20

Special-K99 · Jul 4, 2017

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

oldbrewer · Jul 4, 2017

LASTNAME

FIRSTNAME

DATE

TIME

L1

F1

03/04/2017