Excel Timestamping

Getting a time-in date or timestamp which will stick in a particular cell of excel is useful in a variety of situations. I originally experimented with this idea to be able to issue children's readers in a makeshift library. I barcode scanned the ISBN number and used a vlookup to call the title. Then I used the students ID number to call their name. Last I needed to record the date the book was issued so I wanted an automatic time stamp.

Later I realised I could use a similar system to record when students arrived in a class. Again I leveraged my barcode scanner, by putting a barcode of the students ID onto a notebook that I issued to them. Then when they arrived in class I just scanned their (required) book to get their name and timestamp. If they didn't bring their book, they got chastised and I just typed in their ID number. In class I wasn't particularly interested in recording a time out, however I got many enquiries about having a more rigorous time out. Also my early versions were focused on small classes (<24) which didn't fit others so well.

Actually, I assumed people would be making their own rather than relying on my own limited examples.

Eventually I created a new more versatile version which can handle larger numbers of students and different session times.

There are a few sample files available for download. The videos show the latest version and some of the previous ones.

Features of time-in-time-out.xlsx spreadsheet.

up to 100 students in a class
5 different class start times per week
class list shows who has not yet arrived
time-in recorded by ID number or barcode
time-out generates time attended value
number of students currently present is shown
statistics page shows % of sessions attended
individual class start time adjustable
class size can be extended beyond 100

Does this work on a Mac?

Yes. See this video.

Latest version of Attendance with time in and time out. Sample available below or the link show in the video. 100+ students, who is absent, time spent in class calculated.

NEWS

Just confirmed I can run this fully mobile with my Windows 950XL phone, and even works with the barcode scanner too!

This video shows the development of the time out function.

This next video was prematurely named the "Ultimate Attendance Spreadsheet" ;-) It does show the formulas that are being used so it is a good explanation of the creation of sheet.

Switching on Iterations is crucial for the timestamp to work. Watch this if you are having trouble getting the Now() time to update correctly. Also if you get a Circular Reference warning you will need to go back and repeat these steps of enabling Iterations. For some reason, Excel can lose this setting.

Back to the original - here is one of my first and most viewed timestamp videos.

Notes:

You must switch on Iterations in the formulas options, or the now() function won't work.
The timestamp must be triggered from another cell. It places a time in itself based on something happening in a different (target) cell.
Make sure you set the movement of the Enter key to shift down rather than across.
I protect the sheets to stop inadvertent changes, but there is no password. Just click unprotect.

Files included below

Attendance Sheet code: PDF file - a list of the cell code that was used in the Ultimate Attendance video. This gives you an easy to view list of the now() function and the vlookup function.

attendance Barecode tickbox: XLSX file - a version that uses tickboxes to generate a time out by ticking next to the name of a student.

excel Iterative: PDF file - shows the Iterative calculation selection required to deal with circular references.

time-in-time-out: XLSX file - The latest version which can handle up to 100 students in a class.

Class behaviour - a tick box chart/table for recording and displaying behaviour.

Scorekeeper - a table to record points for 6 teams.