# Clock In/Out Excel Spreadsheet



## tsukasadt (Feb 8, 2012)

i'm sorry if this is a bad place to put this but i'm not sure where else to post. basically i have this excel spreadsheet that will log whenever the button is pushed with the information i want but not the way i want. my vba is very basic as im still learning but i dont have a lot of time to complete this project.what i need is a way to modify the code to distinguish between the names, if that name was last checked out or in, if last checked out to disable the prompt asking for the location theyre going and instead just log the time they returned, and if last checked in to start a new line with all the info. my current problem is that i dont know how to make it detect a previously entered entry let alone whether it was last checked in or not or even how id code that.im sorry ive only been doing vba for a few days and thought id have more time to learn.heres my code:

```
Sub Entry()    Dim r As Range    Dim s As Range    Dim t As Range    Dim u As Range    On Error Resume Next    Set r = Sheets("Master List").Range("Time")    Set s = Sheets("Master List").Range("Rank")    Set t = Sheets("Master List").Range("Name")    Set u = Sheets("Master List").Range("Location")    On Error GoTo 0    If r Is Nothing Then Sheets("Master List").Range("D3").Name = "Time"    If s Is Nothing Then Sheets("Master List").Range("A3").Name = "Rank"    If t Is Nothing Then Sheets("Master List").Range("B3").Name = "Name"    If u Is Nothing Then Sheets("Master List").Range("E3").Name = "Location"    If Sheets("Clocking").Range("B6").Value = "" Then        MsgBox "Who are you!?", vbExclamation        Sheets("Clocking").Activate        ActiveSheet.Range("B6").Select    Else        If Sheets("Clocking").Range("B8").Value = "" Then            MsgBox "Where are you going!?", vbExclamation            Sheets("Clocking").Activate            ActiveSheet.Range("B8").Select        Else            With Sheets("Master List").Range("Time")                .Value = Now                .NumberFormat = "yyyymmdd \@ hhmm"                If .Column = 4 Then                    .Offset(1, -1).Name = "Time"                Else                    .Offset(0, 1).Name = "Time"                End If            End With            With Sheets("Master List").Range("Rank")                .Value = Sheets("Clocking").Range("D3")                .Offset(1, 0).Name = "Rank"            End With            With Sheets("Master List").Range("Name")                .Value = Sheets("Clocking").Range("D4")                .Offset(1, 0).Name = "Name"            End With            With Sheets("Master List").Range("Location")                .Value = Sheets("Clocking").Range("D5")                .Offset(1, 0).Name = "Location"            End With        End If    End IfEnd SubSub Reset()    Dim r As Range    Dim s As Range    Dim t As Range    Dim u As Range    On Error Resume Next    Sheets("Master List").Range("D3").Name = "Time"    Sheets("Master List").Range("A3").Name = "Rank"    Sheets("Master List").Range("B3").Name = "Name"    Sheets("Master List").Range("E3").Name = "Location"    On Error GoTo 0End Sub
```
thank you!


----------



## tsukasadt (Feb 8, 2012)

i'm so sorry, i posted that from work and i guess it just didn't like the site or something, here's how it should've been:


tsukasadt said:


> i'm sorry if this is a bad place to put this but i'm not sure where else to post. basically i have this excel spreadsheet that will log whenever the button is pushed with the information i want but not the way i want. my vba is very basic as i'm still learning but i don't have a lot of time to complete this project.
> 
> what i need is a way to modify the code to distinguish between the names, if that name was last checked out or in, if last checked out to disable the prompt asking for the location theyre going and instead just log the time they returned, and if last checked in to start a new line with all the info. my current problem is that i don't know how to make it detect a previously entered entry let alone whether it was last checked in or not or even how id code that.
> 
> ...




also i wanted to post the actual document but it says it's not a valid file. :S


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

You posted in the right place!!

Not sure why the gods of tsf have limited the file formats the way they did... but zipping will allow you to post it = P. Once it is posted, we can determine a little more of what your macro is doing and what needs to happen. Probably even throw in a few suggestions for you.

Regards,

Robert D. Specian Jr.


----------



## tsukasadt (Feb 8, 2012)

thank you for replying, i was wondering if it would get any hits :-S

i hope this is what you wanted, i didnt even know i could make zips, i thought rar was all ^^'


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

Okay, there are a few things that I would personally change.

The way you are using name ranges works, but it could be "simplified" by using a single named range and offsets. You used a little of it = ) so props! The formula we will be using for the named range will be:



> =INDIRECT(CONCATENATE("'Master List'!$A$",COUNTA('Master List'!$A:$A)+1))


This will give us the cell on the Master worksheet that is the first unused cell. IE where we will put the next row of information. We will use this to reference all other input data. Note, this method will only work if every previous cell has a value. Also, I named it "test" which you will see in the macro.

The tricky part is going to be searching for the last instance of the persons name. You will need to use either a bit of VBA or an array formula. I personally prefer array formulas to longer VBA scripts. The formula is:



> =IFERROR(IF(ISBLANK(INDIRECT(CONCATENATE("C",MAX(IF(B:B=Clocking!B6,ROW(B:B)))))),"Check Out","Check In"),"Check In")


This will tell us whether the last instance of the name we have selected last checked in or out, or whether they have any previous information. If they don't have any previous data or their last information was a check out... then it will list "Check In". If the previous data reads it was a check in, then it will list "Check Out".

If you haven't worked with array formulas before, they must be entered using CTRL+SHIFT+ENTER instead of just ENTER or TAB. For these purposes I simply just added it to the Master List worksheet in cell H1. It doesn't need to be seen.

So, with that named range and the Array formula, we have what this new instance will be (Check in/out) and the first unused cell. Last thing we need to do is write the VBA for it.




> Sub punchclock()
> Dim rw As Integer
> 
> rw = Worksheets("Master List").Range("test").Row
> ...


To wrap up, all you have is three things. A VBA macro to transfer the informaion, an array formula to figure out how your employee last clocked in/out and a named range to figure out what row you are on.

I've attached the workbook I threw together for your. = )

Hope this helps,

Robert D. Specian Jr.


----------



## tsukasadt (Feb 8, 2012)

you just blew my mind :-O

one thing i see in both the history you sent me and my testing it is that it is starting a new line regardless name changes. basically, i can have CT1 Kelly check in at the start of the day and then check out at noon for lunch but when she goes to check back in, with or without another entry in between, it starts a new row. i dont know if maybe i'm using it wrong but between your new formula and VBA i'm at a loss for what to actually look for that'd cause it. :-(

i appreciate your help thus far, i know that what i had was crude and i hope i'll be able to fully understand your code soon (i got Excel VBA Programming for Dummies from the library ^^').


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

Yeah, it is supposed to start a new line. 

What I saw with yours was that you were creating a new line for every check-in/out but combining the check-in/out's. In effect creating a lot of rows without any useful information. To correct that, I just added a new row for each instance... if you need this to change we can change it.

To change it, we just need to change two things.

First, we need the last row of the individual checking out. In I1 you can put:



> =MAX(IF(B:B=Clocking!B6,ROW(B:B)))


Again this is an array formula. CTRL+SHIFT+ENTER

Next we just have to change the VBA to check for it. Not much of a change though.



> Sub punchclock()
> Dim rw As Integer
> 
> rw = Worksheets("Master List").Range("test").Row
> ...


I haven't tested, but that should do it. = )

Regards,

Robert D. Specian Jr.


----------



## tsukasadt (Feb 8, 2012)

this is strange, i'm getting an error message but all it says is "400" :-S


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

Okay, found the problem. The array formula for I1 was referencing the wrong cell. Here is everything again:

H1:



> =IFERROR(IF(ISBLANK(INDIRECT(CONCATENATE("C",MAX(IF(B:B=Clocking!D4,ROW(B:B)))))),"Check Out","Check In"),"Check In")


I1:


> =MAX(IF(B:B=Clocking!D4,ROW(B:B)))


VBA:



> Sub punchclock()
> Dim rw As Integer
> 
> rw = Worksheets("Master List").Range("test").Row
> ...


You should only have to put in the new I1 array formula. = )

Hope this helps,

Robert D. Specian Jr.


----------



## tsukasadt (Feb 8, 2012)

ah, almost perfect! your updated formula works perfectly, just in the opposite direction of the intended effect. ^^'

basically, the first entry for everyone should be only the clock in, indicating the start of the record and the follow on entries should reflect the time they clock out, the reason they clocked out, and then the time they returned. the whole point of this is actually to keep track of how much time our guys are actually at work, that way we as the reviewers can justify giving lower marks for those that aren't actually providing their time to the office as they should be.

i really appreciate all your help on this, i know you made my horrid start of code into something 10x better. once that clock in/out thing gets reversed, which I believe is solely in the code, i can start on the reports feature, which i have an idea on how to do.

just to clarify though, you made a whole new macro with your code, and i see no reference to my original macros, would i be correct in assuming that i can simply delete the original VBA?


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

Hmm, okay, I was wondering why you listed Clock In and Clock out in reverse. Are you trying to watch for the time clocked out or the time clocked in?

With what you're saying it will be much more difficult to calculate the time clocked in...

What I would do is reverse Columns C and D. Having C be the clock in and D the clock out. Then change the things to:

H1:



> =IFERROR(IF(ISBLANK(INDIRECT(CONCATENATE("D",MAX(IF(B:B=Clocking!D4,ROW(B:B)))))),"Check Out","Check In"),"Check In")


I1:



> =MAX(IF(B:B=Clocking!D4,ROW(B:B)))


VBA:



> Sub punchclock()
> Dim rw As Integer
> 
> rw = Worksheets("Master List").Range("test").Row
> ...


This way, a clock in and the subsequent clock out are right next to each other. Also, during a clock in, no reason will be present. Only during clock out will you get the reason for the clock out.

This should keep things a little easier... If you do need it the other way, it is possible but a lot trickier. Since you will have the one row that doesn't contain both pieces of information.

Regards,

Robert D. Specian Jr.


----------



## tsukasadt (Feb 8, 2012)

yeah, i'm sorry, it's not my format just what the boss wants.

i've been playing with it myself and i think i'm on the right track, having only had to modify the original VBA you gave, but now i'm getting the constant new line issue again, and i bet it has something to do with my attempt at using the IsNull() function. :-S

here's my attempt.

H1:

```
=IFERROR(IF(ISBLANK(INDIRECT(CONCATENATE("C",MAX(IF(B:B=Clocking!D4,ROW(B:B)))))),"Check Out","Check In"),"Check In")
```
I1:

```
=MAX(IF(B:B=Clocking!D4,ROW(B:B)))
```
VBA:

```
Sub punchclock()
    Dim rw As Integer
    rw = Worksheets("Master List").Range("test").Row
    If Worksheets("Master List").Range("H1").Value = "Check In" Then
        If Worksheets("Master List").Range("C" & rw).Value = IsNull("C" & rw) Then
            Worksheets("Master List").Range("D" & rw).Value = Now
            Worksheets("Master List").Range("A" & rw).Value = Worksheets("Clocking").Range("D3").Value
            Worksheets("Master List").Range("B" & rw).Value = Worksheets("Clocking").Range("D4").Value
        Else
            rw = Worksheets("Master List").Range("I1").Value
            Worksheets("Master List").Range("D" & rw).Value = Now
        End If
    Else
        Worksheets("Master List").Range("C" & rw).Value = Now
        Worksheets("Master List").Range("A" & rw).Value = Worksheets("Clocking").Range("D3").Value
        Worksheets("Master List").Range("B" & rw).Value = Worksheets("Clocking").Range("D4").Value
        Worksheets("Master List").Range("E" & rw).Value = Worksheets("Clocking").Range("D5").Value
    End If
End Sub
```
the part i think is causing the constant new line at this point is:

```
If Worksheets("Master List").Range("C" & rw).Value = IsNull("C" & rw) Then
```
problem is that i don't understand how it is wrong. all it's doing is saying if C4 IsNull then provide x information else if C4 isn't null provide y information. :-(


----------



## tsukasadt (Feb 8, 2012)

i think i got it! :-D

H1:

```
=IFERROR(IF(ISBLANK(INDIRECT(CONCATENATE("C",MAX(IF(B:B=Clocking!D4,ROW(B:B)))))),"Check Out","Check In"),"Check In")
```
I1:

```
=MAX(IF(B:B=Clocking!D4,ROW(B:B)))
```
VBA:

```
Sub punchclock()
    Dim rw As Integer
    rw = Worksheets("Master List").Range("test").Row
    If Worksheets("Master List").Range("H1").Value = "Check In" Then
        If Worksheets("Master List").Range("I1").Value = "0" Then
            Worksheets("Master List").Range("D" & rw).Value = Now
            Worksheets("Master List").Range("A" & rw).Value = Worksheets("Clocking").Range("D3").Value
            Worksheets("Master List").Range("B" & rw).Value = Worksheets("Clocking").Range("D4").Value
        Else
            rw = Worksheets("Master List").Range("I1").Value
            Worksheets("Master List").Range("D" & rw).Value = Now
        End If
    Else
        Worksheets("Master List").Range("C" & rw).Value = Now
        Worksheets("Master List").Range("A" & rw).Value = Worksheets("Clocking").Range("D3").Value
        Worksheets("Master List").Range("B" & rw).Value = Worksheets("Clocking").Range("D4").Value
        Worksheets("Master List").Range("E" & rw).Value = Worksheets("Clocking").Range("D5").Value
    End If
End Sub
```
it seems to function just as i want it to without any errors, just need to add in prompts for the clock out box for the reason and it should be good. please let me know if you notice any glitches or a better way of doing it! i sat down and broke down the code dummy-style, so i realized i was trying to call out C0, which doesn't exist. ^^'

i'm going to pursue the next step of this project, individual reporting. :-S


----------



## RSpecianjr (Jan 20, 2010)

Hey tsukasadt,

Looks good to me! = )

Let us know if you need help with the individual reporting as well.

Regards,

Robert


----------

