# Recording real time interval after event trigger

#### pkoelkebeck

##### New Member
Please help! I'm not an expert using Excel or with VBA, but I would really like to know how I can perform this operation in Excel. If there is an addin or program I could get, then please point me in the right direction. If you know how I could do it, I would be very grateful for the help.

Here's what I need to do:

I have already set up a real time monitor (in cell m3) that updates from the system time whenever a change is made anywhere on the sheet (or by running a macro to automate the updates). The clock is in hour, minute, and second format. I want to be able to track the exact minutes and seconds from that clock.

Basically, I need to find out how I can start a timer to track the time elapsed after an event occurs. Here's the path I would like the operation to follow:

1. Cell (h12) value reads "GO"

2. That "GO" event triggers a record or log of the exact time on the real-time clock (in cell m3) to be made and displayed in a cell (p2).

3. Another cell (p3) will keep a continuous update of the elapsed time since the log of the exact time the event occured (p2).

4. Final cell (p14) will indicate whether the elapsed time (p3) is greater than or equal to a specified time length (b9) by reading "YES".

I know this may be somewhat complicated, but I need to implement this operation for my research. Any help in the right direction will be greatly appreciated. Thanks so much. -Paul

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
L

#### Legacy 98055

##### Guest
Hi Paul.

The following is an example based upon a few assumptions. You did not mention what the specified time length in (b9) was? A number of minutes and/or seconds?

What happens when (h12) changes from "GO" to some other value?

The example, as is, assumes a number of elapsed seconds in (h9) as the "YES" threshold. If (h12) changes from "GO" to some other value, logging is halted without changing any values.

<a href="http://home.fuse.net/tstom/0710080652.329162.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0710080652.329162.zip">0710080652.329162.zip</a>

Code in the workbook class:
Code:
``````Private Sub Workbook_Open()
Sheet1.StartClock
End Sub``````

Code in worksheet:
Code:
``````Option Explicit

Private RunNext As Date
Private PreviousValue As String
Private IsTracking As Boolean

Friend Sub StartClock()
IsTracking = False
Tick
End Sub

Friend Sub Tick()
Range("M3").Value = Now
RunNext = Now + TimeSerial(0, 0, 1)
Application.OnTime RunNext, Me.CodeName & ".Tick"

If IsTracking Then
Range("P3").Value = Range("M3").Value - Range("P2").Value
If DateDiff("s", Range("P2").Value, Range("M3").Value) >= Range("B9").Value Then
Range("P14").Value = "YES"
Else
Range("P14").Value = "NO"
End If
End If

If Range("H12").Value <> PreviousValue And Range("H12").Value = "GO" Then
Range("P2").Value = Now
IsTracking = True
ElseIf Range("H12").Value <> PreviousValue And PreviousValue = "GO" Then
IsTracking = False
End If

PreviousValue = Range("H12").Value

End Sub

Friend Sub StopClock()
On Error Resume Next
Application.OnTime RunNext, Me.CodeName & ".Tick", , False
End Sub``````

#### Wayne01

##### New Member
Compile error on example occurs at "Friend Sub StartClock()". Well it does excel 97.

Replies
1
Views
450
Replies
8
Views
2K
Replies
4
Views
419
Replies
8
Views
671
Replies
1
Views
852

1,190,916
Messages
5,983,562
Members
439,848
Latest member
Sulrc

### 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.

### Which adblocker are you using?

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

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