Freeze time in a cell

[Steve]

I'm trying to enter "time now" in a cell when I enter any value in an adjacent cell. This is for race timing.
This is easy, but I want it to freeze the valve of the time - the problem is that when I enter anything else in the spreadsheet, the "time now" updates as well. Is there an easy way to "freeze" the original entry?
Many thanks.

 

[rollis13]

One of these examples should do the trick:

=IF(ISNUMBER(F4),IF(G4="",TODAY(),G4),"")
=IF(ISBLANK(F4),"",IF(G4="",NOW(),G4))

You will need to activate the Iteration with Max n. =1 elsewise you will get a Circulation Reference error.

 

[Steve]

One of these examples should do the trick:

=IF(ISNUMBER(F4),IF(G4="",TODAY(),G4),"")
=IF(ISBLANK(F4),"",IF(G4="",NOW(),G4))

You will need to activate the Iteration with Max n. =1 elsewise you will get a Circulation Reference error.

Hi rollis, thanks for reply and set itteration but still doesn't seem to work.

say I enter 3 in b3 I'd like time now, say 11:30:24 to appear in c3
then say 27 in b4 should put current time, say 11:30:42 in c4 but keep c3 at 11:30:24
then say 18 in b5 puts current time, say 11:31:05 in c5, keeping c3 and c4 as they were.
This is to time bike riders as they cross the line.
Any ideas?
Many thanks

 

[rollis13]

Maybe an example is easier:

 

[Sixthsense]

Hi Steve,

You should go for VBA coding approach for achieving this....

The below code inputs the time in Column-B whenever any change occurs in Column-A.

Copy the below code and do right click on sheet tab and select view code and paste it. Close the VBA window (Alt+Q to close VBA window) and return to that sheet and check.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)

[COLOR=darkblue]If[/COLOR] Target.Column <> 1 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]With[/COLOR] Target.Offset(, 1)
    .Value = Now()
    .NumberFormat = "DD-MMM-YYYY HH:MM:SS"
    .Columns.AutoFit
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Refer the attached file for details

 

[Steve]

Many thanks guys, between you both, I've got it to do what I want.
Had a bit difficulty downloading files, but got there.
Thanks

 

[peter.abing]

Hi,

The quickest way to enter the time (now) that will not update (that is hardcoded) is to press ctrl+shift+: (colon).
For date, press ctrl+shift+; (semi-colon).

I hope this is the one you are looking for.

 

[Steve]

Hi,

The quickest way to enter the time (now) that will not update (that is hardcoded) is to press ctrl+shift+: (colon).
For date, press ctrl+shift+; (semi-colon).

I hope this is the one you are looking for.

Hi Peter,
This is where I started - but, when I enter time ( Ctrl + shift + colon) it never enter seconds.
And even if I then change formnat to Hr:min:sec it always shows 00 for seconds regardless of when I do the entry.

Is this just my version of Excel??
Cheers

 

[peter.abing]

Hi Peter,
This is where I started - but, when I enter time ( Ctrl + shift + colon) it never enter seconds.
And even if I then change formnat to Hr:min:sec it always shows 00 for seconds regardless of when I do the entry.

Is this just my version of Excel??
Cheers

Thanks for the info. I did not realize seconds is not included in that shortcut. I don't usually use that shortcut. Now I know what to do if I will need this function. Thanks again.