Download File: Excel Punch Clock
This basic Excel punch clock does exactly what you would expect – it tracks the time of various users in one easy-to-access file. The user setup and interface is easy enough that most people will not need any help in using it. The punch clock has enough security that the casual user cannot change any settings or times, but it is very easy to edit and change if you know some VBA. This Excel punch clock is a step up from a pencil and paper system, but if security is a high concern, I would recommend something more secure than this free download.
How to Use the Punch Clock
Instructions for Users
The instructions for individual users is straightforward. The punch buttons are located on the “Main Screen” tab, so make sure that tab is selected.
To punch in, click on the the big blue “Punch In” button. If your name has not been entered,click the purple button on the bottom left of the dialog box called “Add Name to List” to add your name. You will also need to enter a password. Now that your name is on the list, select it from the name drop-down box from the “Punch In” dialog box. Next, enter your password and click the light blue “Punch In” box. A dialog box will appear reporting the time you punched in.
To punch out, click on the orange “Punch Out” button. Select your name, enter your password, and click the orange “Punch Out” button that is in the dialogue box. Another dialogue box will tell you the time you punched out.
Viewing the Time Summary Report
To see a summary of time that each user has been punched in, click on the “Report Tab”. This pivot table automatically updates when selected to show you how long each user has been punched in.
Instructions for Admin
All the data is stored on the “Data Sheet” sheet, which is set to “Very Hidden” when you open the book. To unhide it – either change the worksheet property in the VBA editor, or click on the “Admin” button. The password right now is “alex-fuller” with no quotations. Additionally, the VBA code also hides the formula bar, headings, and grid lines when it opens, although these options can be changed on the “Data Sheet”. The report tab shows a pivot table summing the time users have punched in each day. It automatically updates when the tab is selected, calculating based on time worked so far if someone is currently punched in.
Why Did I Build an Excel Punch Clock?
I am occasionally in charge of a group of temporary employees that are working on a large project. Keeping track of everyone’s time became a cumbersome task. Everyone wrote down their times on a clipboard, which someone then had to tally up each week. At the very least, this process of entering the times into Excel took at a half hour or more. Additionally, some employees complained that others were showing up a few minutes late but then writing down an earlier time for when they started. Most of it was just honest rounding – I know I usually put 8:00 when I’m signing in as a visitor and the time is really 8:03. Nevertheless, I wanted to be fair and reward those who showed up on time.
Recognizing the need for a punch clock system, I found that most paid systems, including the one our normal employees use, require a fair amount of setup on the part of the administrator. Instead, I wanted something so easy that a new employee could add his or her name to the list without any help. I searched the Internet, but was unable to find anything I thought ideal for my situation. So, I set off to build my own Excel-based punch clock.
Up to this point, I hadn’t used forms very extensively. Most of the VBA code that I had written was for automation, not user experience. This was my first dive into an Excel application meant for unsophisticated users. Fortunately, Excel forms are much more intuitive and easy to use then they appear to be at first. I did find a few weird bugs along the way, such as problems with upper and lower case passwords (I thought Excel didn’t care about case), but most of them have been worked out. My wife kept wondering why I spent so much time late at night with a spreadsheet that only had two big buttons, but even she approved of it once she saw the final product.
Over the next month, I had over twenty people exclusively record their time with my Excel punch clock. I changed it so that it would save after every punch, which is important when people’s paychecks depend on Excel not crashing. Everyone found the program quick, intuitive, and easy to use. The employees were actually able to show each other how to use it without my help or supervision at all – a great success for me.
Having refined it to suit my needs, I released it on the Ozgrid Excel forum. I received a request to add a rounding feature, which I have done. Additionally, I have saved it as a .xlsb file, which may help with the one known issue of sometimes crashing on 64-bit windows. I’d like to share the latest version with you here. Please let me know what you think, stories of how you use it, or ideas for improvement.
For some reason, it sometimes crashes on 64-bit windows, but works great on 32-bit. Ozgrid user dkirkeberg suggested that saving it as an .xlsb file can help. It’s helped me run it on my 64-bit system.
Possible Future Improvements
- More Security
- More in-depth reporting on reports tab
- Ability to clear times without manually deleting them from the table
- Name of project employee is working on to track job costs
Like the Excel Punch Clock Download?
Subscribe to see other files to come, send others here, or comment and tell others why you like it.