Using RFID reader to record attendance.

[dominikus123]

Hello!
I am new to the forum and have a problem which you could solve. I have searched through internet and haven't found suitable tutorial to record attendance. On youtube there is a video where you can create time stamp for in, but not for out: https://www.youtube.com/watch?v=xjv0YYf9ZcQ. This is how far i have came.
So to explain my situation. I work in small company where I want to track attendance. I want to be able to track atendance of multiple employees. I have an RFID reader and RFID cards with different numbers. I've created excel document where when you swipe with card, card number is writen in A2 field, in B2 my identity is written, than in C3 my time in is written. I have this working. Howewer if I swipe with card again, same process begins in A3 field and so on.
After some more searching I found out that I need a macro to do this. On this forum I found one thread that somebody wanted similar thing: http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html
Instead of writing times in same row, I want only in and out time, than i want cursor to move in new row.

To show it in steps what I want:

When John swipes the card for the first time this happens:

ID	NAME	IN	OUT
5664254	John	13.14

<tbody>
</tbody>

Than somebodey else comes this happens:

ID	NAME	IN	OUT
5664254	John	13.14
4321565	Bob	14.12

<tbody>
</tbody>

Than john goes home:

ID	NAME	IN	OUT
5664254	John	13.14	17.43
4321565	Bob	14.12

<tbody>
</tbody>

Bob goes home:

ID	NAME	IN	OUT
5664254	John	13.14	17.43
4321565	Bob	14.12	18.31

<tbody>
</tbody>

Than bob comes next day I want this:

ID	NAME	IN	OUT
5664254	John	13.14	17.43
4321565	Bob	14.12	18.31
5664254	John	7.22

<tbody>
</tbody>

And so on.....

I hope you understand what I want and somebody will be able to help me. I appriciate every help I will get from you guys

 

[pbornemeier]

You stated:

I've created excel document where when you swipe with card, card number is written in A2 field, in B2 my identity is written, than in C3 my time in is written.

It seems odd that the time would be offset by a row, did you mean C2 for the time?

I believe your system is working like this: The first scan appears on Row 2 with Number in column A, Name in column B and time in column C.
The next scan of another card puts its data in row 3, is that correct?

What would happen if the row 2 data were deleted after it was received? Would the next scan appear at row 2, or is there a tracking mechanic of some type that would put it at row 3, even if row 2 was blank?

Does each day start again at row 2? or do the filled rows accumulate until reset by a user?

It would not be hard to write code to scan each new entry and then check the output sheet to see if an entry for that card number was present with 0 or an even number of entries (which would indicate that the most recent scan was an arrival) or an odd number of entries (which would indicate the current scan was a departure).

However there is a potential problem with this method. If the user swipes their card and does not get feedback that the card was read immediately then they might swipe the card again. This would result in the following:

Number Name In Out
5664254 John 07.22 07.22

So when they swipe out for their noon meal

Number Name In Out
5664254 John 12.11

It would appear as if they just arrived. And when they returned 45 minutes later

Number Name In Out
5664254 John 12.11 12.56

Even if they get feedback, they might swipe twice by accident.

To prevent this there are a few options:
1) Provide feedback when the card is swiped to show/say welcome when they arrive or goodbye when they leave (preferred).
2) 2 readers, one for arrive, one for depart
3) Include in software to ignore an exit swipe within 2 minutes of an arrival swipe (this may result in a valid swipe being ignored, if for instance, someone swiped in, immediately realized they forgot something in their car and swiped out.

Do work periods ever cross the midnight boundary?

 

[dominikus123]

Hello!

It seems odd that the time would be offset by a row, did you mean C2 for the time?

I believe your system is working like this: The first scan appears on Row 2 with Number in column A, Name in column B and time in column C.
The next scan of another card puts its data in row 3, is that correct?

-Yes pbornemeier you are correct I meant C2 field.

What would happen if the row 2 data were deleted after it was received? Would the next scan appear at row 2, or is there a tracking mechanic of some type that would put it at row 3, even if row 2 was blank?

-If data in row 2 is delited everything would be fine. It would just continue in next row, so in your explanation in row 3

Does each day start again at row 2? or do the filled rows accumulate until reset by a user?

-No I want each day to start in new row for every person who uses system that day

It would not be hard to write code to scan each new entry and then check the output sheet to see if an entry for that card number was present with 0 or an even number of entries (which would indicate that the most recent scan was an arrival) or an odd number of entries (which would indicate the current scan was a departure).

yes I tried this metod but can't get it to work to write data in same row. In my case I managed to made something like this:

ID	NAME	IN	OUT
5664254	John	13.14
4321565	Bob	14.12
5664254	John		17.43

<tbody>
</tbody>

and than for in time again in next row. Just couldn't get it to work to write time out in same row as time in.

However there is a potential problem with this method. If the user swipes their card and does not get feedback that the card was read immediately then they might swipe the card again. This would result in the following:

Number Name In Out
5664254 John 07.22 07.22

So when they swipe out for their noon meal

Number Name In Out
5664254 John 12.11

It would appear as if they just arrived. And when they returned 45 minutes later

Number Name In Out
5664254 John 12.11 12.56

That wont be a problem because reader beeps when card is read.

Even if they get feedback, they might swipe twice by accident.

I havent put much thought into that, but yes this is potential problem, so it might be good idea to make a program to ignore entry if card is swiped twice in less than 2 minutes.
And work periods newer cross the midnight boundry. However I would like that possibility just in case. I think in ODD or EVEN logic, time over midnight wouldn't be a problem.

If you could make this, or point me in right direction that would be great, because currently I am stuck where it is.