7
NEW ANSWER
If you want to have a timestamp that does not recalculate you will need to use a technique called Circular Formulas. This basically allows a cell to perform a function based on its own content.
You need to enable this functionality by going to File > Options > Formulas then ticking the Enable iterative calculation
. Change the Number of iterations to 1
. Press OK and save your sheet.
=IF(A1<>"",IF(B1="",NOW(),B1),"")
Explanation:
The first IF
is similar to the one in the original answer. It checks if A1
is blank. <>
is the logical operator meaning not equal to
. The second IF
checks itself and runs if content is entered into A1
. If B1
is empty it enters the current date and time otherwise it outputs the existing content (the original timestamp).
ORIGINAL ANSWER
You could try this in B1:
=IF(ISBLANK(A1),"",NOW())
Explanation:
ISBLANK
is a logical test that tests if the target cell has any input. It returns either TRUE
or FALSE
.
The IF
is structured so that if the target cell is blank it will output an empty string. If there is any input entered into A1
it will output the current time and date.
It should be noted that each time the sheet is recalculated the value outputted by NOW()
will change to the current time.
References:
A well-crafted answer! – Gary's Student – 2015-12-10T12:16:06.923
THANK YOU, first comment is that I have changed "," commas to ";" in order to work out and second commet: How to avoid recalculation? – stagml – 2015-12-10T12:16:45.283
@stagml I suspect that is down to your regional settings but happy to have helped! – Burgi – 2015-12-10T12:18:28.293
How to avoid recalculation? – stagml – 2015-12-10T12:20:28.827
this is it? Menu > Excel Options > Formula > Calculation Options> Manual > Tick off "Recalculate Workbook before Saving" – stagml – 2015-12-10T12:51:57.017
Edited answer to explain circular references – Burgi – 2015-12-10T13:20:06.083
1@stagml I can't say that enabling circular references for this sheet won't cause issues down the road as it appears it's an application property. It'd probably be easier to copy - paste special - values. I gave this answer +1 earlier, so don't see this as me saying it's a bad answer. – Raystafarian – 2015-12-10T17:11:36.350
Came here to answer this question with this answer! Circular references will be fine so long as that's all this particular spreadsheet does. I used this for an automatically timestamping call log. If you needed to do any analysis I'd recommend copying and pasting the raw data into another spreadsheet with circular references deactivated. – Miller86 – 2015-12-11T14:45:18.970
0
It's not always wise to use VBA for everything, but this is a good candidate, especially if you want to track whenever a row was changed, and not just when it was first entered. Insert the following into the code for the sheet with the timestamp column:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim intersection As Range
' Change A:A,C:E to whatever range you want to watch.
Set intersection = Application.Intersect(Target, Me.Range("A:A,C:E"))
If Not intersection Is Nothing Then
Dim r As Range
For Each r In intersection.Rows
' Change B1 to whichever column is the timestamp.
r.EntireRow.Range("B1").Value = Now
Next
End If
Application.EnableEvents = True
End Sub
In this example, columns A, C, D, and E are watched for changes, and when changes do occur, the current date and time is inserted into column B of the same row.
It should be noted that placing code in the Worksheet_Change() event will effectively disable undo for that worksheet because the undo stack is wiped every time VBA code that impacts workbook values is executed. – Ross McConeghy – 2017-04-24T20:29:18.160
1Do you really want to fill the function
=Now()
, so it'll always show the current date; our you want to create a "timestamp" to know when data in the cell was entered? – Máté Juhász – 2015-12-10T12:18:49.353timestamp is an option, what is the relevant function? – stagml – 2015-12-10T12:47:00.970