Creating clock in and out with just a click of a button

YooooowaN

New Member
Joined
Mar 19, 2005
Messages
33
I'd like to create a spreadsheet wherein employees can just click on a button each time they go in and out of break. I'd like to monitor who's going on over break. Is this possible?

Thanks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
YooooowaN, welcome to the board.
There are a number of things to consider as far as how your sheet is set up, but basically you just need to time stamp.
Here's one of many possibilities. Unless you want just one button (one in / out time for the entire group) it would require a button and a designated row in the worksheet for each person. If you put this button say in row 5 column A and put a person's name in row 5 column B, you could track the times horizontally across the sheet. For that you could use something like:
Code:
Sub TimeInTimeOut()
  With Range("IV5").End(xlToLeft)(1, 2)
    .Value = Time
    .Font.Size = 8
  End With
End Sub
I've changed the font size to make any time of day fit into a standard width column.
There are so many ways this could be done that you'll have to decide what you want.

Hope it helps,
Dan
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Hi,
You can ofcourse create a macro to get the system time into cells to check how long they are out. But, What if they are going out with out clicking?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Yes, you can do what you want. The following will prompt for an employee # and input the date & time in Sheet1:
Code:
Private Sub CommandButton1_Click()
    Dim yourelate As String
    Dim LastRow As Object
        yourelate = Application.InputBox("Please enter employee ID #", "Employee ID", Type:=1)
        Set LastRow = Sheets("Sheet2").Range("A65536").End(xlUp)
        
        With LastRow
            .Offset(1, 0) = yourelate
            .Offset(1, 1) = Date
            .Offset(1, 2) = Time
        End With
        
End Sub

You can also check out the timesheet solutions at www.ozgrid.com for some ideas.

Note that anything's possible, but (and it's a big but) you're relying on employees to be honest about their time, specifically, when they go over their alotted break time. What's to keep someone else from logging in for them? Excel's security is notoriously weak, so someone can pretty quickly figure out how to alter/defeat an Excel solution.

As someone who has punched timecards for late emloyees (in my youth), I can tell you that no system is infallible. You're relying on the "locks on doors" principle, in that they only keep honest people honest.

We use phone log in's for our telemarketers (which can easily be defeated by a co-worker logging in for someone else), and biometric readers for our operations folks. Now that's a good investment, because it can't be cheated (unless you give someone your hand).

From what I've found, the best way to manage employee timeliness or tardiness is to actually manage to it. If there's a problem, you keep the records, not them. Cheaters and slackers will try their damndest to break whatever system you put in place; only good management skills and intuition will bust that.

Sorry for the rant (but we deal with it all the time) & hope that helps,

Smitty

(Heya Ace! Nice rainy day here and I'm looking at/drooling on my Chugach Mountain Guide (avoiding housework))
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Hiya Smitty!
Beautiful spring day here. (After about 2 weeks of 3 to 4 inches of rain a day (I measure it at work) and steady 60 to 80 mph winds with at least one 12 hour period of 90 mph+ & gusts up to 120! (Literally...) - Normal lousy whether for us at this time of year.

(Some of those mountians are about 40 feet from my bedroom window and go **** near straight up from there.)

In my experience, as long as the housework is done before the boss gets home you should be alright :LOL: )
Dan
 

YooooowaN

New Member
Joined
Mar 19, 2005
Messages
33
Thanks for your response (sorry it took me sometime to reply).

I'm quite new to VB, can you tell me where I can paste these codes (peenysaver)?

To add, will this also work on different worksheets (each employee has its own) where the employee's log in infos will be saved on a file that I myself can only access (all files are saved on a network server) for tracking purposes?

Say they each have a clock in and out button on their respective files. The employee's date, time and employee number (can we add another option - 1st, 2nd and 3rd brk?) will then be saved on a single worksheet.

and...

Is it also possible to automatically get the computer's date and time once they press the button so the actual login time is entered?

Thanks again for all your help!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

I'll give you some steps to use the code that I posted on multiple sheets:

1) Open the VB Editor (ALT+F11)
2) Goto Insert-->Module
3) Paste the posted code in the new window that opens on the right
4) Change the name from Private Sub CommandButton1_Click() to-->
Sub Timestamp
5) Adjust the referenced ranges as necessary
6) Exit the VBE (ALT+Q)
7) On a test sheet draw a button from the Forms toolbar and when prompted to assign a macro, select "Timestamp"
8) Click it and go.

Post back how it works for you and it can easily be adjust to fit your needs.

As for the system Date & Time, that's what the VBA functions "Date" & "Time" will enter"

As for the breaks being entered on a different sheet, you'd need to be more specific, but this ought to be a start.

Smitty
 

YooooowaN

New Member
Joined
Mar 19, 2005
Messages
33
Hi, many many thanks!

I was able to repro it. I was thinking if its possible to add a prompt wherein they are also asked to enter if they'll be taking their 1st, 2nd, or 3rd break; or, if they are reporting back after each break (a single prompt for this one will do).

As an example:

Press the button
The first prompt in the program asks you for your employee ID
The next one will ask for what type of break you are about to take

As an overview, I am planning to have the same button on 20 files. Each employee has their own and these files are all connected to one excel file. So I need all the date, time, employee ID, and the type of break they will be taking all saved in one excel file (all files by the way are saved on a company server). Is there any way we can also get the filename (xls) on which the data came from (to prevent cheating)?
 

YooooowaN

New Member
Joined
Mar 19, 2005
Messages
33
I was able to add a new prompt. :biggrin: I just don't know the proper syntax to have all the data saved on a different workbook. Your inputs are appreciated.

Instead of saving the data in Sheet2, I'd like to have it saved on a different workbook (ex file name c:\documents and settings\default\my documents\DbaseLOG.xls)

==========================================

Set LastRow = Sheets("Sheet2").Range("A65536").End(xlUp)


Thanks!!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
If possible, I'd reconsider individual workbooks and use worksheets within the same wb if possible. You can always use the xlVeryHidden property to ensure that your users don't see them and can't unhide them. Otherwise, you'll need to open the employee workbook first, transfer the data, then close it, which isn't that hard, but might be an issue for employees who are trying to get in "under the wire"...

But if you post the code that you came up with and add the correct path someone can ammend it to do what you want.

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,659
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top