Lock in today date, how to?

D

_dave

New member
Joined
May 11, 2016
Messages
21
Reaction score
0
Points
0
Excel Version(s)
2013
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
 
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:
.
Will it still work as required if you send the file to another person who has not "turned on iteration" ?
 
.. 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....
 
You must log in or register to reply here.
Back
Top