"Time Clock" Functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

valiant - 30 May 2008 15:49 GMT

Hello,
I'm looking to create a macro with similar function to that of a time clock
used in work shops (one that when activated will copy the current time
(preferably seperated from the date) into another cell for use with other
equations). It should also fill it in like a list whenever the macro is
activated (so it fills in, for example, cell A1 when the macro is first
activated and the next time it is used it will fill in the current time in
A2). Can anyone help me with this? I appreciate it.

Reply to this Message

Rick Rothstein (MVP - VB) - 30 May 2008 16:10 GMT

Something like this maybe...

Sub TimeStamp()
Const TimeStampColumn As String = "A"
Dim X As Long
Dim LastUsedRow As Long
With Worksheets("Sheet1")
LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row
.Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _
Value <> ""), TimeStampColumn).Value = Now
End With
End Sub

Rick

> Hello,
> I'm looking to create a macro with similar function to that of a time
[quoted text clipped - 5 lines]
> activated and the next time it is used it will fill in the current time in
> A2). Can anyone help me with this? I appreciate it.

Reply to this Message

valiant - 30 May 2008 16:22 GMT

Hey Rick,
Thanks for the help, that is a great macro for getting the time and filling
it in like a list. I really appreciate that. My only question remaining is,
is there a way to add to the macro so that it takes the newly recieved time
and changes it into a format that only displays the time and keeps the date
seperate (or better yet, assigns the date to one column and the time to
another)? Thanks again for the help.

> Something like this maybe...
>
[quoted text clipped - 20 lines]
> > activated and the next time it is used it will fill in the current time in
> > A2). Can anyone help me with this? I appreciate it.

Reply to this Message

Rick Rothstein (MVP - VB) - 30 May 2008 16:35 GMT

This macro put the date in Column A and the time in Column B...

Sub DateTimeStamp()
Const DateStampColumn As String = "A"
Const TimeStampColumn As String = "B"
Dim X As Long
Dim LastUnusedRow As Long
With Worksheets("Sheet1")
LastUnusedRow = .Cells(Rows.Count, DateStampColumn).End(xlUp).Row
If .Cells(LastUnusedRow, DateStampColumn).Value <> "" Then
LastUnusedRow = LastUnusedRow + 1
End If
.Cells(LastUnusedRow, DateStampColumn).Value = Date
.Cells(LastUnusedRow, TimeStampColumn).Value = Time
End With
End Sub

Rick

> Hey Rick,
> Thanks for the help, that is a great macro for getting the time and
[quoted text clipped - 35 lines]
>> > in
>> > A2). Can anyone help me with this? I appreciate it.

Reply to this Message

valiant - 30 May 2008 16:51 GMT

Hey Rick,
I gotta thank you. All of your suggestions have really answered all of my
questions and I think I have a good grasp of this now. Thank you for all of
your help and I will post if I have any more questions in the future.

> This macro put the date in Column A and the time in Column B...
>
[quoted text clipped - 54 lines]
> >> > in
> >> > A2). Can anyone help me with this? I appreciate it.

Reply to this Message

valiant - 30 May 2008 17:18 GMT

One last question I just came across during my attempts. I realized if I
rename the worksheet or try to get the macro to work with another worksheet,
it doesn't work anymore and I get an error message. How can I make it so
that this macro can be used with whatever the worksheet is named and
potentially with multiple worksheets (So if I were to activate the macro in
sheet 1, it would show the results in sheet 1, then I could activate it in
sheet 2 without changing the macro and it would show the results in sheet 2)?
Sorry for the barrage of questions, I'm kinda new to macros in Excel.
Thanks for the help.

> Hello,
> I'm looking to create a macro with similar function to that of a time clock
[quoted text clipped - 4 lines]
> activated and the next time it is used it will fill in the current time in
> A2). Can anyone help me with this? I appreciate it.

Reply to this Message

Rick Rothstein (MVP - VB) - 30 May 2008 17:38 GMT

In my last posted routine, change this line...

With Worksheets("Sheet1")

to this...

With ActiveSheet

Rick

> One last question I just came across during my attempts. I realized if I
> rename the worksheet or try to get the macro to work with another
[quoted text clipped - 19 lines]
>> in
>> A2). Can anyone help me with this? I appreciate it.

Reply to this Message

valiant - 30 May 2008 19:29 GMT

Thanks for all your help, Rick. you've answered all my questions for me and
it works great now. I really appreciate it.

> In my last posted routine, change this line...
>
[quoted text clipped - 29 lines]
> >> in
> >> A2). Can anyone help me with this? I appreciate it.

Reply to this Message