Dynamic Input Timer

[Ndel40]

I would like have code in the sheet module that calculates the elapsed time
it takes a user to enter data into the spreadsheet. For example... A user
first data entry is in column "A" and last data entry is in column "E". I
would like to start a clock when they enter the data in column "A" and stop
it when the data in column "E" is entered and output to elapsed time in
column "F". Data will continue to be entered in descending order by row
every hour or so... that's why I believe the code needs to be dynamic in the
sheet module.

Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time
1 2 3 4 5 0:01:42
1 2 3 4 5 0:02:12
1 2 3 4 5 0:02:42
1 2 3 4 5 0:03:12

Any suggestions would be appreciated... thanks!

 

[Jacob Skaria]

The below will point you in the right direction...To make this robost you
will need to check the row number while picking the start time and validate
that when you output the time interval..Select the sheet tab which you want
to work with. Right click the sheet tab and click on 'View Code'. This will
launch VBE. Paste the below code to the right blank portion. Get back to to
workbook and try out.

'Format ColF to excel time format

Private Sub Worksheet_Change(ByVal Target As Range)
Static myTime As Variant
If Target.Count = 1 Then
If Target.Column = 1 Then
myTime = Now
ElseIf Target.Column = 5 Then
Application.EnableEvents = False
Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400
Application.EnableEvents = True
End If
End If
End Sub

 

[Ndel40]

Works Great... Thanks!

The below will point you in the right direction...To make this robost you
will need to check the row number while picking the start time and validate
that when you output the time interval..Select the sheet tab which you want
to work with. Right click the sheet tab and click on 'View Code'. This will
launch VBE. Paste the below code to the right blank portion. Get back to to
workbook and try out.

'Format ColF to excel time format

Private Sub Worksheet_Change(ByVal Target As Range)
Static myTime As Variant
If Target.Count = 1 Then
If Target.Column = 1 Then
myTime = Now
ElseIf Target.Column = 5 Then
Application.EnableEvents = False
Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400
Application.EnableEvents = True
End If
End If
End Sub

 

[Ndel40]

You are correct, I did some testing and it needs to be a bit more robust and
I need to add some additional criteria. I would like it to only start the
timer if the cell in column "A" is blank and stop the timer when the data is
entered into column “E†one row below the row where the timer started… in
other words the data input range is 5 columns by two rows.

Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time
Start Timer 2 3 4 5
6 7 8 9 Stop timer 0:02:12

 

[Jacob Skaria]

Private Sub Worksheet_Change(ByVal Target As Range)
Static myTime As Variant, myRow As Long
If Target.Count = 1 Then
If Target.Column = 1 Then
If myTime = "" Then myTime = Now: myRow = Target.Row
ElseIf Target.Column = 5 Then
If myRow = Target.Row - 1 Then
Application.EnableEvents = False
Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400
myTime = ""
Application.EnableEvents = True
End If
End If
End If
End Sub

If this post helps click Yes

 

[Ndel40]

It works nice for the timer. However, the start timer only on a blank cell
doesn't seem to work... that's OK for now... I need to do some real time
testing with the users to see if it is an issue or not.

Thanks for you help!!!