# Thread: Freeze time in a cell

1. ## Freeze time in a cell

Register for a FREE account, and/
or Log in to avoid these ads!

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.

2. 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.

3. Originally Posted by 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.
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

4. Maybe an example is easier:

5. 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:
```Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 Then Exit Sub

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

End Sub```
Refer the attached file for details

6. 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

:-)

7. 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.

8. Originally Posted by 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.
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

9. Originally Posted by Steve
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•