I have set up a formula in Excel to record the time for riders in a bike race
who are doing a number of laps. Each time they come over the line I want to
put their race number in to a cell and for Excel to record the time. I have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I put
another race number in to the cell below. Does anyone know how I can do this?
Sarah

I assume you are using the NOW function. Yes, that will cause the time to
change everytime the sheet is calculated. You can use teh NOW function to
first enter the time, but then you will want to immediately copy and paste
values the time so that it doesn't change. You would have to do this with a
macro if you want it to happen automatically. Put the code in the change
event of the worksheet you are using.

"Girlie Bloat" wrote:

I have set up a formula in Excel to record the time for riders in a bike race
who are doing a number of laps. Each time they come over the line I want to
put their race number in to a cell and for Excel to record the time. I have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I put
another race number in to the cell below. Does anyone know how I can do this?
Sarah

Hi Sarah,

http://www.mcgimpsey.com/excel/timestamp.html


--
Kind regards,

Niek Otten

"Girlie Bloat" wrote in message ...
|I have set up a formula in Excel to record the time for riders in a bike race
| who are doing a number of laps. Each time they come over the line I want to
| put their race number in to a cell and for Excel to record the time. I have
| successfully created a formula to do this (if the value of the cell into
| which I put the race number is 0 the time is inserted into another cell).
| However, once the time has been inserted I don't want it to change when I put
| another race number in to the cell below. Does anyone know how I can do this?
| Sarah

If I understand your question correctly you are using the now() function in
your spreadsheet and the problem is that every time you add a new rider, the
now function re-evaluates to the current time for all of the cells. You want
the time to stick once you have enetered the rider? The problem is that Now()
is volatile and changes every time the claculation runs. My solution would be
to add a standard code module (record any macro to do anything and it will
insert a standard cde module called Module1, ...) In that module add the
following code.

Public Function RightNow() As Date
RightNow = Now()
End Function

This will make a non-volatile version of the now() function called
rightnow() that you can use in place of the now function, something like...

=IF(A10, rightnow(), "")
Where your rider number is in Cell A1
--
HTH...

Jim Thomlinson


"Girlie Bloat" wrote:

I have set up a formula in Excel to record the time for riders in a bike race
who are doing a number of laps. Each time they come over the line I want to
put their race number in to a cell and for Excel to record the time. I have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I put
another race number in to the cell below. Does anyone know how I can do this?
Sarah

If you want to use a formula, you would need to go to
Tools=Options=Calculation tab and click the iterations checkbox to allow
intentional circular references. Set max iterations to 1

assume if cell A1 has a 1 in it, you want B1 to show the time it was placed
there

=IF(A1=1,IF(OR(B1="",B1=0),NOW(),B1),"")

use a custom number format under Format=Cells and use hh:mm:ss.000

another approach would be to

select the cell and do Ctrl+Shift+: (colon)

Or you could use a macro.

Sub SetTime()
Activecell.Value = Now()
End sub

--
Regards,
Tom Ogilvy






"Girlie Bloat" wrote:

I have set up a formula in Excel to record the time for riders in a bike race
who are doing a number of laps. Each time they come over the line I want to
put their race number in to a cell and for Excel to record the time. I have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I put
another race number in to the cell below. Does anyone know how I can do this?
Sarah

Thanks to all of you who so quickly responded to my plea. I used Tom's
formula and it seems to work. It's obviously only going to be as good as the
caller and typist but it's a much cheaper option than timing chips!
Thanks again to you all.
Sarah




"Tom Ogilvy" wrote:

If you want to use a formula, you would need to go to
Tools=Options=Calculation tab and click the iterations checkbox to allow
intentional circular references. Set max iterations to 1

assume if cell A1 has a 1 in it, you want B1 to show the time it was placed
there

=IF(A1=1,IF(OR(B1="",B1=0),NOW(),B1),"")

use a custom number format under Format=Cells and use hh:mm:ss.000

another approach would be to

select the cell and do Ctrl+Shift+: (colon)

Or you could use a macro.

Sub SetTime()
Activecell.Value = Now()
End sub

--
Regards,
Tom Ogilvy






"Girlie Bloat" wrote:

I have set up a formula in Excel to record the time for riders in a bike race
who are doing a number of laps. Each time they come over the line I want to
put their race number in to a cell and for Excel to record the time. I have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I put
another race number in to the cell below. Does anyone know how I can do this?
Sarah

http://groups.google.co.uk/group/mic...ad61 707957a0

RBS


"Girlie Bloat" wrote in message
...
I have set up a formula in Excel to record the time for riders in a bike
race
who are doing a number of laps. Each time they come over the line I want
to
put their race number in to a cell and for Excel to record the time. I
have
successfully created a formula to do this (if the value of the cell into
which I put the race number is 0 the time is inserted into another cell).
However, once the time has been inserted I don't want it to change when I
put
another race number in to the cell below. Does anyone know how I can do
this?
Sarah