Freeze time in a cell

Steve

New member
Joined
Mar 7, 2014
Messages
4
Reaction score
0
Points
0
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.
 
Last edited:

rollis13

Member
Joined
Feb 22, 2013
Messages
82
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
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.
 
Last edited:

Steve

New member
Joined
Mar 7, 2014
Messages
4
Reaction score
0
Points
0
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

Member
Joined
Feb 22, 2013
Messages
82
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
Maybe an example is easier:
 

Attachments

  • Example TimeStamp.xls
    14 KB · Views: 22

Sixthsense

New member
Joined
Nov 6, 2012
Messages
26
Reaction score
0
Points
0
Location
India
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.

Code:
[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 :)
 

Attachments

  • Worksheet_Change_Event_Code.xlsm
    12.5 KB · Views: 13

Steve

New member
Joined
Mar 7, 2014
Messages
4
Reaction score
0
Points
0
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

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
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

New member
Joined
Mar 7, 2014
Messages
4
Reaction score
0
Points
0
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

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
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.
 
Top