Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Lock-in TODAY date?

  1. #1
    New Member
    Join Date
    Jan 2006
    Location
    Alpine, UT
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lock-in TODAY date?

    Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

    I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

    Thanks in advance!

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,902
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default

    CTRL semicolon
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    New Member
    Join Date
    Jan 2006
    Location
    Alpine, UT
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry to be dense. At what point to I do CTRL semicolon? Will that do it automatically? I'm trying to figure out how to have it lock in automatically as soon as it pulls up the date.

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default

    If you type in CTRL + ; an unchanging date will be entered.

    Alternatively type =TODAY()

    (don't press Enter)

    and press F9.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Jan 2006
    Location
    Alpine, UT
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What does F9 do in this situation?

    Here is my formula: =IF(N5="","",TODAY())

    This will pull up today's date if there is any value in N5. If N5 is blank, it will remain blank.

    What I'm trying to figure out is how to make it freeze the date once somebody puts a value into N5 so that I never have to touch it.

    PS, CTRL ; is a great little shortcut. Don't know why that one's never crossed my path before.

  6. #6
    Board Regular
    Join Date
    Jun 2006
    Location
    Devon
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a tad ragged but should work.....

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    a = Date
    If Range("n5").Value <> Blank Then
    If Range("a1").Value = Blank Then
    Range("a1").Value = a
    Exit Sub
    End If
    
    If Range("a1").Value < a Then
    Exit Sub
    End If
    Range("a1").Value = a
    End If
    
    End Sub
    ......I think :o
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Microsoft Office Specialist Master (2013)

  7. #7
    New Member
    Join Date
    Jan 2006
    Location
    Alpine, UT
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Disco, I'll give it a shot.

  8. #8
    New Member
    Join Date
    Jan 2006
    Location
    Alpine, UT
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BTW, if anybody knows how to do it outside of VBA, it'd be real helpful since I'm still in the learning stages of VBA.

  9. #9
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is a way, fraught with danger. If you enable circular references (Options\Calculation and check "Iteration") you can write formulas that self-reference. Enable CR's, clear the contents of cell N5, and enter the following formula in cell O5:
    Code:
    =IF(NOT(O5=0),O5,IF(ISBLANK(N5),0,TODAY()))
    (I've assumed that column O is where you want the date.) Once this is in cell O5, copy it down as far as you'll need, plus a few rows.

    1. The formula first looks to it's own cell to find out if there's a date there already; if so the formula puts that date back in the cell. (For this reason, if you need to extend this formula down to more rows later, you must copy it from a cell whose result is zero, not a date.)
    2. Then the formula looks at cell N5; if it's NOT empty, the formula puts today's date in the cell.

    You'll now have zeroes in those cells until you enter something in the corresponding cells in column N. (Anything: text, numbers, formulas, they all trigger the ISBLANK logic in column O.)

    The danger is you are now unprotected from ACCIDENTAL circular references in other cells. If that's not a concern, the above should work.
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

  10. #10
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,902
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, Dan.

    There is no way to conditionally enter a static date without using VBA.

    A couple of follow-up questions:

    (1) What should happen if someone *changes* N5? The date cell change to today's date (i.e. show the date of the last update)? Or, do you just want the date cell to show the *first* time N5 was updated?

    (2) Also, what if someone deletes the value in the date cell? (or do you want to prevent that from happening)?
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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