Results 1 to 9 of 9

Thread: Freeze time in a cell

  1. #1

    Question 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.
    Last edited by Steve; 2014-03-07 at 10:10 AM. Reason: spelling

  2. #2
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    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 by rollis13; 2014-03-07 at 05:51 PM.

  3. #3
    Quote Originally Posted by rollis13 View Post
    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. #4
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    Maybe an example is easier:
    Attached Files Attached Files

  5. #5
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    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
    Attached Files Attached Files

  6. #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. #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. #8
    Quote Originally Posted by peter.abing View Post
    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. #9
    Quote Originally Posted by Steve View Post
    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
  •