Prev: Reference text in a cell inside a formula to specify a sheet name
Next: 2007 Pivot table Calculated field misbehaving
From: pat67 on 19 Feb 2010 14:20
I am pretty sure I ahve seen this but not 100%. How do you set a cell
to run a timer like for a sporting event?
From: Bob Bridges on 20 Feb 2010 17:55
It happens I have to do this when checking my speed in an on-line browser
game called Deepolis. Runs from one station to the next typically take from
one to three minutes, and in order to gauge my speed exactly I do a few trial
runs every couple of months. There's more than one way to do this, but
here's my method:

a) Fill up two columns (Leave and Arrive) with a dozen or so rows of =NOW().
Each trial run will use one row; the rest of the rows are just for
subsequent runs. Every time I change something in the sheet, or hit <F9> to
recalculate, all these values are updated to the current date and time; but
if I hit <F9> and get 17:28:13, it stays at that value until the next time
Excel recalculates. Keep that in mind for the following:

b) When I start out from a station, hit <F9>. The values are updated to the
millisecond that I hit the button, and display let us say 13:04:57.

c) I have a minute or three before I arrive at the target station; that
leaves me plenty of time to copy the Leave cell of this row and repaste the
Value (not the formula) back into it. When as soon as I do, the Leave cell
displays the value 13:04:57 but all the rest of the cells, still having
=NOW() in them, display perhaps 13:05:06.

d) Now I wait for my sub to arrive at the target station; when it does, I
hit <F9> again on my spreadsheet. The Leave cell still displays 13:04:57,
but now all the =NOW() cells show 13:06:38.

e) At my leisure I copy the Arrive cell and paste its value (not its
formula) back. Now the Leave cell shows 13:04:57, the Arrive cell shows
13:06:38 and all the rest of the =NOW() cells show (for example) 13:06:45. I
don't care about them; they're for subsequent trial runs.

f) Other cells on this row subtract the Leave starting time from the Arrive
value to show travel time, divide the travel time by the known distance from
one station to the next to give me the sub's speed, and so on.

This should work for you UNLESS you can't take the time for the keystrokes
to convert the formula to the value (which in Excel 2003 are
<Ctrl-c><Alt-e>sv<Enter>. In that case...hm, in that case I'm not sure what
would be better for you. No doubt you could write a button in the sheet, and
a VBA program that records start and stop times each time you push the
button. Whether that would be worth the trouble for you is another question;
if you want to look into that, re-ask the question in the Excel Programming
forum.

--- "pat67" wrote:
> I am pretty sure I ahve seen this but not 100%. How do you set a cell
> to run a timer like for a sporting event?
 | 
Pages: 1
Prev: Reference text in a cell inside a formula to specify a sheet name
Next: 2007 Pivot table Calculated field misbehaving