Elapsed times corresponding to cell values

[wynhowe]

Hi All,
I have been struggling with this for a while now and was wondering if anybody had any suggestions on how to go about it.
I need a method of getting elapsed times assigned to cell values. To explain further- if, say cell AK5 has either a value of 1 or -1 then when it =1, seconds counter starts in cell AL5, or AM5 for -1.The cell(AK5) will change many times during the event so the counters would have to retain previous seconds and just add on further seconds whenever the change occurred. I have limited understanding of Excel and VBA in particular so don't know if this is easy or complex. AK5 will always be at 1 or -1 but going to a new market they (cells AL5 and AM5) would have to revert to zero. AL5 and AM5 will always increment by 1 for every second depending on value in AK5.Also it would have to cater to all rows i.e. AK5,AK6,AK7,---------AK40.Basically a stop watch that doesn’t revert to zero until market change.
Many Thanks

[alrodopial]

vba is needed

[wynhowe]

I am aware that VBA is needed but how do you (for instance) stop a simple counter reverting to zero when the value in AK5 changes from 1 to -1 or -1 to 1?

[Captain Sensible]

You just need to take a timestamp when a certain event occurs then compare that timestamp against the current time to get the difference. Here's a simple routine I use in an automated sheet that gets the in running seconds then resets once the market is suspended so it's ready for the next market.

If Cells(2, 5) = "In Play" Then
If Cells(1, 24) = "" Then Cells(1, 24) = Cells(2, 3)
If Cells(1, 24) <> "" Then Cells(2, 24) = DateDiff("s", Cells(1, 24), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 24) = ""
Cells(2, 24) = ""
End If

[Captain Sensible]

I am aware that VBA is needed but how do you (for instance) stop a simple counter reverting to zero when the value in AK5 changes from 1 to -1 or -1 to 1?

You'd just use the timestamp cell to control any further changes, so the timestamp would be set from nothing to a time value once your condition in AK5 was met. The counter has it's own set of rules to only start counting once the timestamp value is no longer nothing "" so isn't reliant on AK5

[wynhowe]

Thanks C.S. for the info. I'll try to implement the above and see how I get on. Much appreciated.