Apr 23 2018
- last edited on
Jul 25 2018
I am trying to get the date/time that a cell changes but not have it autoupdate....
Using this: =IF(B2 <> "",NOW(),"Not Checked Out") works until you update the next row an hour later then everything changes to the current NOW date/time. I can't use "TODAY" because it doesn't give me the time.
Apr 23 2018 06:03 PM - edited Apr 23 2018 06:03 PMSolution
what you want to achieve cannot be done with the NOW() formula. It will always update to the current date and time. If you want a time stamp that does not change, then you will need to use VBA.
This code will put a time stamp into column B when a cell in column A is changed by the user. Copy the code, then right-click the Sheet, select "View code" and paste the code into the code window.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Offset(0, 1) = Now End If End Sub
Apr 23 2018 08:27 PM
Here you go:
1. open excel
2. go to: File > Options
3. in popup window go to : Formulas
4. Check "Enable iterative calculation"
5. Change the value of "Maximum iterations:" from 100 to 1
6. click OK
Now suppose you have to enter the value A1 and get the output (Time) in B1 enter the following formula in B1
Apr 23 2018 10:01 PM
@Gourab Dasgupta Yes, this is possible, but it can have implications for other aspects of the workbook, where iterative calculation is not desired. Unless the user is absolutely sure what they are doing with this setting, I suggest avoiding this approach.
Apr 24 2018 05:53 AM
You are right, but as per present scenario the user is getting the exact output without need of having knowledge of advance excel. let her go through the solution and wait for the response. we are here to guide.
Apr 24 2018 09:46 AM
Thank you all, I ended up using the VB Script as that was the only way to ensure that the dates and times didn't change if someone accidentally clicked to calculate the sheet.
Aug 02 2020 01:10 PM
Thank you @Ingeborg Hawighorst . This little piece of code solved a perplexing problem for me. A next step is to save the workbook as a macro-enabled workbook (.xlsm). I did not do that and when I reopened the file, code was gone. Luckily I found it again, along with a post with the save info. I backed up the code to a text file for safe keeping.
Aug 05 2020 04:39 PM
@PCDavis61618 When you write VBA code in Excel that does not yet have any code, and then save the file, you will see a big, fat warning that VBA code can only be saved in macro-enabled files and you are prompted to save the file as a macro-enabled file. If you just ignore that warning, your code will be lost, of course.
Excel has done its part. Now it's up to you to do yours.