Results 1 to 5 of 5

Thread: Lock in today date, how to?

  1. #1
    Acolyte _dave's Avatar
    Join Date
    May 2016
    Posts
    21
    Articles
    0
    Excel Version
    2013

    Lock in today date, how to?



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

    I have a field (columns - NAME) and another called DATE

    When the NAME cell has a name, I need the DATE field filled in and it cannot be changed.

    So far I have tried the following:

    Cell b3 has "NAME", Cell d3 had "DATE", so

    Cell b4 has "Dave" and d4 has 09/11/18. I am using the following command in d3: =IF(B4>'',NOW(),'')

    By that I mean if cell b4 is blank do not insert today date, if it is not blank enter today date.

    My problem is that the date will change each day, I want it to stay on the date when a name was put in the name field for that day.

    Is there a way I can do this and keep the date it was entered?

    Thanks ahead for any suggestions.


    -dave

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    You can't do that with formulas... you'll need a worksheet event macro....

    https://social.technet.microsoft.com...la?forum=excel


  3. #3
    Acolyte _dave's Avatar
    Join Date
    May 2016
    Posts
    21
    Articles
    0
    Excel Version
    2013

    Smile

    Hi NBVC,
    In fact I did find a way using formulas. Thanks to Sumit Bansal at https://trumpexcel.com/date-timestamp-excel/ for his tip.
    I could not find how to post a doc file, so here is my solution (images did not paste).
    ------------
    When just using the “=NOW()” function it will always change the date from day to day.

    So, to use it as a “timestamp” you will have to make one change in the spreadsheet options. And that is to “turn” iteration on.


    For Microsoft Excel do the following:

    1. From Excel Options dialog box, select Formulas.

    2. In the Calculated options, check the Enable iterative calculation option.

    For Libre Office Calc do the following:

    1. From LibreOffice Calc Tools, select Options.
    2. Under LibreOffice Calc, select Calculate.
    3. Check the Iterations box, then click ok.

    In your date column (using B2 as a reference for date and A2 for name) enter the following formula:

    =IF(A2<>””,IF(B2<>””,B2,NOW()),””)

    That it. Now when you enter a name (or anything) in column A, a time stamp would automatically appear in column B that will not change.
    Last edited by _dave; 2018-09-12 at 06:12 PM. Reason: font size

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    221
    Articles
    0
    Excel Version
    2007
    .
    Will it still work as required if you send the file to another person who has not "turned on iteration" ?

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    .. and if for some reason later in time, you are told to switch iteration off because it was causing some other workbook to not work as intended... you will not realize that re-opening this workbook again you will have lost all your dates... it's a risky solution....


Tags for this Thread

Posting Permissions

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