clock in excel

[cryfortheclown]

Hi there,
I was just wondering if there's any way to display the actual in-running time of a race once the race turns in-play on the excel sheet.
Thank you

[Captain Sensible]

You'll need to use VBA as I don't think it can be done with formulas, just log the time the market indicator switches to "In Play" and then take the difference between that and the current time.

[cryfortheclown]

Thanks for reply, - and how would I go about doing that, Captain? To be honest I don't know anything about VBA

[Captain Sensible]

VBA isn't any harder than formulas once you get started have a look at this thread to understand how to enter the code viewtopic.php?f=5&t=9800#p50270

Then enter the following code under sheet1 or whatever you called your sheet that get updates from Gruss. It can be stripped down a lot but it'll future proof you if you start getting more complicated code later on. It simply looks for E2 to display In play then timestamps the current time in cell AA1 (format that as time or it'll show 0.02222 or similar ) It'll then put the number of seconds the race has run in cell AA2 and stops when the race suspends showing the full duration. Once you enter a new market it'll clear the cells.

Code: Select all

    Option Explicit


    Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    On Error GoTo xit
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False 'turns off events so we don't retrigger the code
    Application.Calculation = xlCalculationManual 'turns off all calculations

With Target.Parent

If .Range("A1").Value <> MyMarket Then ' setup any market parameters or clear old cells
MyMarket = .Range("A1").Value
.Range("AA1:AA2").Value = "" 'clear the timer for a new market
GoTo xit
End If
   
If .Range("E2").Value = "In Play" And .Range("AA1").Value = "" Then .Range("AA1").Value = .Range("C2").Value  'timestamp the off time'
If .Range("E2").Value = "In Play" And .Range("F2").Value = "" Then .Range("AA2").Value = DateDiff("s", .Range("AA1").Value, .Range("C2").Value)



End With

xit:
    Application.EnableEvents = True ' events back on
    Application.Calculation = xlCalculationAutomatic ' calculations back on
    End Sub

[alrodopial]

another idea using formulas is something like the below:
viewtopic.php?f=3&t=932

[Captain Sensible]

another idea using formulas is something like the below:
http://www.gruss-software.co.uk/forum/v ... ?f=3&t=932

alrodopial's method is probably the way to go so you can avoid VBA

[cryfortheclown]

Brilliant thanks so much Captain S. & alrodopial