Need help with Punch in and Punch out clock system please.

Good day all

I don't know if there is already a post like this, but I was wondering if anybody will be willing to assist me.

The company I work for uses a payroll program, but all the employees days, working hours, and overtime hours had to be input by means of manual job costing input. I have now recently changed it a bit, so I can do all the work in excel, and then just import it into the payroll program. This made the process go quicker, but I want to streamline it more.

I was wondering if it is possible for the employees to "clock in" by means of a bar-code, and the person's employee number, name and obviously date and time be posted to a sheet, and when they "clock out" it should record the "clock out" time in the next cell to the right of the "clock in" time. My aim is to record the times in and out, then calculate the difference in time per day, and then by means of vlookup to record the hours worked per person per day for the bi-weekly period to another sheet.

I have researched numerous videos, and posts, but cannot find a way to do this (that is how I came upon this forum.

Needless to say... i have hit a wall, and it feels like I have reached the limit of my knowledge.

The picture attached is a sample of how I would like to use the "clock in / out" page, by means of a form.

If anybody could assist me with this, it would be greatly appreciated.

Thank you in advance for any input.

Regards

Originally Posted by Keebellah

Hi, welcome to the forum.
First, I am assuming that there is no punch clock on the premises, if there was maybe this registered somewhere, but I guess that you already went down that road.
This file you have created, is it EXCEL and is it on a network drive / share or local?
If the user logs on to his/her system you could also write a single line of text to a text or csv file telling you the username, the date and time spent , the same when he she logs out of the system.
These triggers can be initiated using the windows scheduler and if done correctly the network engineer could program it remotely.
It all depends how you want to collect the data and what you want EXCEl (it it's in Excel) to do with the data.
Hope to hear from you (if I didn't scare you off with all the questions )

Thank you for your response.
1. You are correct. There is no punch clock on the premises.
2. The file is created in Excel with plenty of macros, and my idea is for it to be at a fixed pc, where people will clock in and out every day. The idea is to have it in a "Dropbox type file" where I can access it from my computer when need be.
3. The people are all labourers, and dont work on computers, so I could not use log in times on their computers.

Basically, I want to record the data in a sheet in Excel (lets say punchin sheet), and then have another sheet within the same file (lets say timesheet) which will then use a lookup function to get the data from "punchin" to display the working hours per day in the "timesheet" sheet.

My problem is that I need to find a way for the labourer/person to "clock" in and out by means of code, or scanner. When the persons identity card is scanned by barcode scanner (or pin number entered), I want excel to write the persons pay number, name & surname, and time punched in to the "punchin" sheet, and when they punch out/clock out for lunch, then excel must write a timestamp in the same line as the persons time-in on the "punchin" sheet. Then when the person returns from lunch and clock's in again, it must again write the persons pay number, name & surname, and time punched in to the "punchin" sheet, and when the person leaves for home, and clock-out, then it must again write the timestamp behind the time the person clocked in after lunch.

I hope this makes more sense of what I am trying to do. And I would like to than you again for your assistance.

Originally Posted by Keebellah

There are barcode scanners and software to do just that.
You scan a barcode, in this case the person's identity card or company card and then just press an IN or OUT button and go.
The other way is the user to click his / her name on a list, enter a password which they may choose the first time so that nobody logs on another user's name.
Do you have a copy of the file with the punchcard you can share? Add two or three ficticious names and I'll see what I can do for you as goes the Excel part.

Thank you again for your assitance.

The file attached named sample.xlsm is the current file i am using to record the persons days worked. The file called "sample punch clock.xlsm" I received from someone else. The log in system pretty much works the way i want it, but it only records 1 clock in time and 1 clock out time per day, and after the week is finished, it finalizes the sheet, and renames it. I want to incorporate his clock in system with my program, but I want to keep all the records in 1 sheet, and then let excel pull over the information via vlookup, or something.

Unfortunately I had to zip the sample file, because it was too big to upload as xlsm file.

Originally Posted by ironfelix717

Hi,

This is actually a pretty simple approach. How to implement a barcode hardware system is not my forte. But you could adapt your code in the future for it.

I would recommend...

Write a userform for a login. User logs in with pass and username. User clicks button to either clock in or out. Every time the clock in button is pressed, you stamp the time and date to a "database" sheet. To avoid confusion and allow analyzing the data easy, you could write the status to a column adjacent to the timestamp. Which has the condition either "clock in" or "clock out".

Pull this "database" spreadsheet from a main reporting macro sheet and analyze it to your hearts content.

Where do you store the passwords? You can hardcode them in the login macro. But they won't be dynamic so adding a user or altering a password will be a bit tedious. You could include login credentials for each user in the database file and when the user goes to log in, you query their name and find the pass. If it matches, waalaa.


I've attached a really down and dirty userform example. Not near to the extent i describe above. But all you really need to do in simple terms is input data to a "database".

Thank you for your input.

The barcode part of it I kinda got sorted. I am using a barcode font, and when I scan the barcode it works in the above userform.

The way your sheet works, is pretty much what I want to happen, BUT I would like the clock out/punch out time to be in the same line/row as the clock in time. That way I can calculate the time worked. The reason I have to calculate it like that, is because when they go on lunch, that time has to be deducted (as lunch time is not paid time). And if they work after 18h00, they get a different tariff, and it will be easier to calculate if it is in the same line/row. So on average every person should have 2 clock in and 2 clock out times per day. Then I can use the sumif and/or vlookup function in a different tab to obtain the actual working hours per day, and pay the person according to that.

The passwords are stored in a separate "database" sheet within the excel file. If you look at the "sample punch clock.xlsm" file I posted, the passwords are in column X under the "Employee Record" tab. They are in white, but it's the same as the employee ID.

Thank you again for your help end effort guys.

Im not too concerned about the passwords at this moment, because the people that clock in or out will only see the login form. The sheet with the passwords will be hidden, and only the administrator will have access to that. The person will in fact not even know what the password is, because it will be disguised as a barcode font. So they don't have to physically press any buttons, they just have to scan the barcode on their ID card. That part I have figured out already.

I actually would like to implement the check if the user is still clocked in. But I have hit a wall guys. Like a mental block.

Firstly I don't know the code needed to write the info from the login screen to the sheet

Secondly I don't know the code to tell the program to check if the person is clocked in, and if he/she is clocked in, how to write the clock out time in the same line, and append the clock in and clock out time so it is written in the same line in the time sheet. The lunch issue I can work out after that.

I appreciate your help.

Thank you.... I really appreciate it.

Originally Posted by Keebellah

Just to make sure I've got it right, the identifier is the Employee ID column W in the Employee Record worksheet?

Good morning. Thats is correct yes

Originally Posted by Keebellah

Just to let you know I started a little. The attached file is just a teaser.
I've left all worksheets visible and the Main_Sheet is the one to work with.
Before you edit the VBA code stop the timer, this will then not interfere.
The timer is triggered on opening and will unschedule on closure to avoid issues.

I started on the code checking the max in and max out
The code will permit to clock in a person once and that's it.
All it does is add a record in the LOG sheet with the emp id, name clockin and clockout (not implemented yet) clock out and the column next to it calculates the total time between if both are filled.

I wa even thinking that all you need is this file with the log sheet, but not the employee records to look them up.
You could read the employee file on-the-fly (open it read only) to verify emp id and name and then close it again.
So then all you need is export the log sheet when you want to create the lists or whatever you do with it.
Right now you have to enter the employee id by hand but as you mentioned that the barcode reader can enter that field for you then you don't need anything else than just open the form, click in or out and it's done.
The user shouldn't need to enter the time, the clock does this, so that files is only necessary to show that the emp is clocked in when clocking out?

Off to bed now. Cheers

Good morning people.

Keebellah
I have looked at the example. The format of the employee log is exactly what I want. ClockIN, ClockOUT and then the difference between the 2. Then the person can clockIN again on the same day, and clockOUt again, and then I can use the SUMIF and VLOOKUP formulas to get the information I need in another sheet.

You are correct in saying that the employee must not enter the time, because that could then be altered, and thats no good. They must just scan the barcode, and the program should automatically clock them IN or OUT depending on the status.

Thank you for your effort thus far. I really appreciate it.

Wow Keebellah.... WOW WOW WOW WOW WOW!!!!!

Thank you so very much. This is WAAAAAAY more than I asked for.

Going to start testing it today still.

Thank you again from the bottom of my heart!

Originally Posted by Keebellah

It is also possible to have the log file 'outside' the main file so that you can always access it (read only) for reporting and checking if all logs are correct.
Minor modifications such as destination folder (where you want the logfile) is easily added.
I would go for that option so that the App can always remain open for Clocking IN / OUT and automatically create a new log file if the month changes

That sounds like a great idea, but would it not interfere if the separate log file is open? For instance is someone is working in it?

Then again... if the data is being pulled in automatically, then the log file would not be seen as open. Am I correct in my assumption?

I just picked up a hickup.

I clocked 2 persons in, then waited a couple of minutes, and clocked them out. no problem. Then I clocked them back in again, also... no problem. Now, when I want to clock them back out again, it only shows the clock in button again. It would not let me clock them out again. Any thoughts?

Hi Keebellah

For some odd reason mine does not want to work when I post the code. I mean there is no error messages, but it still would not allow the check out button to show when I want to clock out the people.

I have attached my files thus far. I added a date column to the employee log tab, and I also added a employee data sheet (EMPDATFIL.xlsx), and then made some minor changes to the timesheet to pull the time worked over.

You sir, are a magician. WOW... Truly remarkable work!

Thank you very very very much!

Originally Posted by Keebellah

Just for your information.
The only time the Employee log file is not read-only is the short span of time that it takes to write the new record.
So if you access the file as read-only (this is important) for reporting purposes there is no hitch when it comes to the punch-clock application accessing this same file. If you do not do this the record cannot be written and an error is raised and I did not take this into account when coding the module.

Hi Keebellah

Thank you, that is good to know.

Firstly.... thank you once again for all your help and assistance. It is greatly appreciated. You solved a problem I have been sitting with for weeks. Thank you.

Secondly... I wonder if you could help me with 1 mall change. On the screen where the person clocks in/out (PunchClockForm), I changed the order of the TabIndex. I want to make it so that when the password is entered, it outomatically jumps to the CheckIn or CheckOut tab/button, but it has no effect after I changed it. Instead of going to the CheckIn/CheckOut button, it goes to the ClearEntry button. Any ideas about what I am doing wrong, or is a line of code that needs to be inserted?

Thank you again for this magnificent project.

Regards

Originally Posted by Keebellah

There are two methods, you set the tab order (under view) and the other way is that focus is set using the vba code.
The way it works now is that after you enter the ID (password) the focus is either set to the CI button or CO button depending on the result.
So when it's a new ClockIn the focus is set to button to register the Check IN, the other the focus is set to the Check OUT
When you do not enter anything an just press Tab the next stop is the CI confirm button, but because it's disabled it tries the next one which is the CO confirm button, but this one is disabled too so the next is the Clear Entry.
I hope this explains it

That is how I understand it, but it does not follow that route. When I enter a password, it automatically goes to Clear Entry. It's almost like the Check In and Out buttons are not there

Thank you sir

Originally Posted by Keebellah

I checked and tried all, but there is no option.In my case it goes to the Close button.
Sorry for that, tried everything I know.

Thank you for your effort, I really appreciate it.

Will fiddle around with it some more. Maybe I will get one of those light bulb moments when I least expect it

Apologies for only coming back to you now. I was out of office whole day.

I looked at the modification, and it works well. Thank you again sir.

Hi guys

Just an update... I've been testing the system, and it works quite well.

I have attached a copy of how it works, and the files used. I have added quite a lot of formulas in my own sheet as
well (to lookup and add the times for specific days, work out overtime, and and and)

Last final obstacle I have come across, is that a when the person clocks in/out, enter(carriage return) needs to be
pressed 3 times (when password is entered, on second box, then on clock in/out button). Is there perhaps a piece
of code that can be added for the program to do that automatically?

Thank you again for all your help. You made this possible.

Good morning sir.

Thank you, that works just fine. I initially wanted it to enter the clockIN/clockOUT button too, but the more I think about it, the more I realise that it is better to manually press the enter (by mouseclick or on the keyboard). That way it is possible to make sure that the employee is in deed clocked in/out.

Thank you very very much.....

Originally Posted by Keebellah

I wonder if you actually calculate the time to the minute or round the clock time off
I wrote this simple function to return the time rounded to the nearest 15 minutes. Using the formulas seen and used in different samples I combined it.

Please Login or Register  to view this content.

The result is the same as if you would use NOW() but rounded to the nearest 15 minutes.
If you use this when actually registering the ClockIn or the ClockOut you won't have to do the calculations yourself.

At the moment I actually prefer to clock the person to the second. It helps to eliminate people who play for time(waste time). But is not a bad idea. Where do I paste the code in?

Originally Posted by Keebellah

Hi, I changed some tab settings and tab order and a minor line of code added in both of the confirm buttons
Now when you enter an ID the confirm CI or CO will focus and after pressing then the close button will get focus so you just strike enter to close the form.

I didn't give your file a new version number so don't overwrite your own before testing.

I prefer it to go back to the password box, because a few people clock in and out at the same time

Originally Posted by Keebellah

Did you manage to modify the code?

Looks like I messed it up

When I modified the code, the program froze when I tried to run it.

Luckily I made a copy

Originally Posted by Keebellah

Just remove the two lines of code: (marked in red)

Please Login or Register  to view this content.

sorry... I should have been more specific....

When I modified/entered the now15 code, the program froze when I tried to run it.

That is working just fine thank you.

Again... you are my hero. Thank you very much for all your effort and help sir.

Will do, thank you

Hi All,
I Love this page,
And i have tried downloading the punchclock to use with a barcode scanner but i cant get it to work,
can anyone help?
I would like my employees to scan their barcore on the way in and out each day, very simple
with a excel table by name,Monday date , Tuesday date , wednesday date , thursday Date, friday Date, Saturday Date, Sunday Date
and underneath the dates in line 2 it will give me the hours work per day

Originally Posted by JasM

Hi All,
I Love this page,
And i have tried downloading the punchclock to use with a barcode scanner but i cant get it to work,
can anyone help?
I would like my employees to scan their barcore on the way in and out each day, very simple
with a excel table by name,Monday date , Tuesday date , wednesday date , thursday Date, friday Date, Saturday Date, Sunday Date
and underneath the dates in line 2 it will give me the hours work per day

I have a file that calculate all the hours. Send me what you got/want, and I will try and modify it for you. Its actually a SUMIF and LOOKUP formula that I used.

Originally Posted by Keebellah

Hi Eugene,
I just suggested JasM to start his/her own thread

ok... cool

hi all

I come up with a Debug .
When i open debug
I get the error in yellow

Application.Caption = Captiontxt
Application.EnableEvents = True
Worksheets(mSheet).Activate
ActiveSheet.Protect UserInterfaceOnly:=True
Dim zRange As String

Originally Posted by Keebellah

Okay, I'll look at your files and see what's wrong.
In the meantime I implemented the 'external' log file.
You will have to indicate the folder where you want the log file to be placed.
A new log file will be created automatically on the first day of each new month upon the first action with the file
The logs filename will be YYYY-MM-Monthname ful-out.xlsx
No provisions made yet if the works night shift and he logged in the last day of the month and wants to log out the first day of the new month.
The new log file is a copy of the hidden EMPLOYEE_LOG file, so if you add columns there these will be copied too.

Hellow Keebellah,

I'd like to make something like this for just myself to track my daily hours. We don't clock in or out here so it's an hour system. But with that, sometimes my boss forgets to write down my OT. I'd like to be able to more accurately track it. I just want something easy that I can click a button to clock in, clock out and it logs the dates, times, and total hours automatically. Would be cool if it would auto calculate the OT (anything beyond 8 hrs). I just want to be able to audit my paychecks. Thanks for any help.

HI Keebellah,

I am trying to open this Excel on MacBook MS Excel 2019 and i am getting error .
1. This workbook contains content that isn't supported in this version of Excel
2.Microsoft Visual Basic compile error - The code in this project must be updated for use on 64-bit systems.

I don't have access to upload images thats why i pasted the error messages.

Could you please help me to resolve these issues in your excel.