Excel Punch Clock

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.

Punching In

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.

Punching Out

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.

Known Issues

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.

Comments 22

  1. Jac wrote:

    Hey Alex,

    This is a great tool and I could really use it if I could get it to work. My job still runs 2003 so I dont think it is converting properly. Get errors in datasheet and also when I enter the password after punch in or out.

    Any clue what could be the issue other than my company needs to upgrade?

    Thanks!
    Jac

    Posted 30 Sep 2012 at 11:47 PM
  2. Alex wrote:

    Hi Jac,

    Unfortunately, the punch clock uses a couple features that Excel 2003 doesn’t have. However, I will put backward compatibility on my to-do list and let you know if I find an easy way that will let it work with 2003.

    Good luck – hopefully they’ll upgrade soon.

    Alex

    Posted 30 Oct 2012 at 10:07 PM
  3. Mark wrote:

    What version of Excel was this made for?

    Posted 07 Nov 2012 at 4:16 PM
  4. Alex Fuller wrote:

    It works with 2007 and above.

    Posted 28 Dec 2012 at 3:14 PM
  5. Ben H. wrote:

    Alex. This is great! Thanks for making such a valuable resource. It will be great for helping me track volunteer time.

    Perchance might I ask how I might go about removing the password validaiton for punching in and out?

    If you dont have time to respond, that’s cool. This is great anyways!

    Posted 21 Jan 2013 at 7:38 AM
  6. shawn wrote:

    Hi Alex. Thank you for this! Would you by any change willing to make a video tutorial on this? PLEASE!!!

    Posted 27 Feb 2013 at 1:54 AM
  7. Jeff wrote:

    Is there a way to delete the sample data and sample users without destroying the pivot table? To use this for my organization I need to start fresh. Thank you.

    Posted 08 May 2013 at 2:35 PM
  8. Teri wrote:

    I’m actually using this to track my own time working on different projects. I’m using the name of the project-Teri, with a one-letter password because I’m lazy.

    Do you clear the data after a pay period is closed, or do you keep the data perpetually? It seems that if you keep it forever, your pivot table results would become very long.

    Posted 09 Sep 2013 at 10:17 AM
  9. Doug wrote:

    Alex; It appears to be a great program, however, after entering an employee and punching them in, it doesn’t register their name on the data sheet and when you go to punch them out, the error message advises that they may not have been punched in. Also, I want to be able to edit entries or add time sheets to the data sheet. Is this possible?

    Thanks.

    Posted 25 Oct 2013 at 2:13 PM
  10. davida@setpointusa.c wrote:

    Hey there Ales. I love the Punch clock!! How hard would it be to put in an drop down menu for Project working on?

    Posted 19 Nov 2013 at 8:16 AM
  11. davida@setpointusa.c wrote:

    Alex…. Sorry

    Posted 19 Nov 2013 at 8:16 AM
  12. Sam wrote:

    This is an awesome tool! Alex you are the man!!!!!!!!!!!!I have set the passwords for employees to show up as ***. And I have added a column in Table for employees to click which project they are working on. I would like to make it so if an employee forgot to clock in or out a box would pop up where they could manually type what time they should have clocked in or out and have the post in the in or out time with a not that it was hand entered. Any Ideas on how to do that?

    Posted 20 Nov 2013 at 5:22 PM
  13. Spencer harp wrote:

    I have a card scanner and employee ids . The scanner returns a unique I’d number followed by essentially an enter. In a simple spreadsheet if you scan the ids it will insert them one after another. Could you modify the vba to utilize this scanner. Instead of employee typing name have them scan their id.

    Posted 30 Nov 2013 at 9:32 PM
  14. Trevor wrote:

    We use this for our High School Robotics team. 74 members… saves ALOT of time. Thanks.

    Posted 10 Jan 2014 at 8:42 AM
  15. Jack B wrote:

    I found this when I was searching the web for a how to on building something like this.

    I’m just using it to track time I spend at the job (I’m self employed) but it is working great! I will eventually use it for hourly employees to punch in.

    Thanks for the great work on this!

    Posted 23 Jan 2014 at 8:03 AM
  16. Linda wrote:

    We also have been using this for a robotics team and like very much. We are hoping for additional help to make it useful for different locations. Is there any way to embed this in a website and have it linked to an online server?

    Posted 19 Feb 2014 at 4:28 PM
  17. Jesus Moreno wrote:

    You are my hero!!!!

    Thank you,
    from PERU

    Posted 12 Jun 2014 at 11:39 AM
  18. shane wrote:

    I love your spreadsheet but I was needing some modifications. Could you contact me?

    Posted 28 Jun 2014 at 8:36 AM
  19. Ben wrote:

    Hi Alex, thanks for a great tool, looking forward to trying it out!

    I don’t know if its because I’m using my mac, but once I add staff names, they don’t appear on the drop down. I have tried adding them under the admin page, but still no joy. Am i doing something wrong? Also can multiple users have this open at the same time? or would it be better suited being on one PC?

    Again thanks for a great piece of script.

    Posted 07 Sep 2014 at 5:36 AM
  20. Dustin Culver wrote:

    Thanks for the great start of a program!

    Posted 09 Sep 2014 at 2:51 PM
  21. google plus api wrote:

    Excellent website. Lots of helpful info here.
    I’m sending it to some friends ans also sharing in delicious.
    And obviously, thanks on your sweat!

    Posted 13 Sep 2014 at 12:33 AM
  22. Irvin wrote:

    Is there a way to delete the sample data and sample users without destroying the pivot table? To use this for my organization I need to start fresh. Thank you.

    Posted 21 Nov 2014 at 3:55 PM

Post a Comment

Your email is never published nor shared. Required fields are marked *