PDA

View Full Version : Time Tracking



Chrismx222
08-13-2007, 05:46 AM
I am a user of Excel. The only part I don't really know is in depth codes, Visual Basic, and Macro. I am looking for a code that has a start time and a finish time that is automatically generated off of two other columns that have names in them. This is for work where I'm trying to track who does specific jobs and to track their time. Example
Enter in a name in column A. Then automatically in column D a time stamp shows up of when that name was entered. After the work is complete the person comes back to the computer and places their name in column B. At that time another time stamp shows up in column E which is the finish time. In column F I would like to create a code in which it tells me how much time has passed between column D and E. Thank you for your help! -Chris

Bob Phillips
08-13-2007, 06:01 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 3).Value = Now
.Offset(0, 3).NumberFormat = "dd mmm yyyy hh:mm:ss"
Me.Cells(.Row, "F").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[-1]<>""""),RC[-1]-RC[-2],"""")"
Me.Cells(.Row, "F").NumberFormat = "d ""days"" hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Chrismx222
08-13-2007, 06:17 AM
THANK YOU THANK YOU THANK YOU!!!
This is exactly what I needed. Thank you!!!
Enjoy your day.
-Chris

Chrismx222
08-13-2007, 07:36 AM
I am trying to run this code on the same sheet twice in different columns. I would like to keep the code in columns A-F. I would also like to run this code in columns J-O. I cut the code and re pasted it at the bottom of the first one. I'm getting an error code of, Ambiguous name detected: Worksheet_Change. What am I doing wrong? Thank you for your help once again. -Chris

Bob Phillips
08-13-2007, 07:43 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B,J:K" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 3).Value = Now
.Offset(0, 3).NumberFormat = "dd mmm yyyy hh:mm:ss"
If .Column < 3 Then
With Me.Cells(.Row, "F")
.FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[-1]<>""""),RC[-1]-RC[-2],"""")"
.NumberFormat = "d ""days"" hh:mm:ss"
End With
Else
With Me.Cells(.Row, "O")
.FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[-1]<>""""),RC[-1]-RC[-2],"""")"
.NumberFormat = "d ""days"" hh:mm:ss"
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Chrismx222
08-13-2007, 12:50 PM
Sorry, maybe I'm not as good with Excel as I thgough. :banghead: Come to find out the columns have changed. I went into the sheet code and changed the "A:B,J:K" "F" and "O" with: "C:D,K:L" (**note the smily face is a : D with no space**) "H" and "P" respectivly. The only column that doesn't work is the "H" Don't know why.
For the last time Thank you!
-Chris

Bob Phillips
08-13-2007, 12:57 PM
You also have to change



If .Column < 3 Then


to



If .Column < 5 Then

Chrismx222
08-13-2007, 01:10 PM
Thank you again!!!
Thank you!!
-Chris