How to Insert Timestamp in Excel When Cell Changes (2 Effective Ways)

This article illustrates how to timestamp in excel when a cell changes. You may need to keep track of data entries in the cells of a particular column. For example, you have reserved column BΒ  to input data. Now you want the timestamp in an adjacent cell in column C when a cell is updated in column B. This article will help you to do that in 2 effective ways.


Download Practice Workbook

You can download the practice workbook from the download button below.


2 Ways to Insert Timestamp in Excel When Cell Changes

1. Use IF, AND, NOW and Other Functions to Insert Timestamp in Excel

Follow the steps below to get a timestamp using formulas when a cell changes.

πŸ“Œ Steps

  • First press ALT+F+T to open Excel Options. Then go to the Formulas tab. Next, check the Enable iterative calculation checkbox. Then, set the Maximum Iterations to 1. After that, click OK.

  • Now, enter the following formula in cell C5. Then, drag the Fill Handle icon to copy the formula to the cells below.
=IF(AND(B5<>"",D5<>B5),NOW(),IF(B5="","",C5))

Timestamp in Excel When Cell Changes with Formula

  • Then, enter the following formula in cell D5. Next drag the Fill Handle icon to the cells below as earlier.
=IF(B5="","",IF(OR(C5="",AND(ISNUMBER(D5),B5=D5)),D5,B5))

Timestamp in Excel When Cell Changes with Formula

  • Now, start entering values in the cells in column B. After that, you will get the following result. Here, column D is a helper column. You can hide it by right-clicking after selecting the column.

Timestamp in Excel When Cell Changes

  • Alternatively, you can just enter the following formula in cell C5 to get the same result.
=IF(B5<>"",IF(C5="",NOW(),C5),"")

  • You may need to change the formatting of the cells in column C. Select the column by clicking the column number at the top. Then press CTRL+1 to open the Format Cells dialog box. Now, click on the Custom number format. Next, enter d-mmm-yyyy hh:mm:ss AM/PM in the Type field. Finally, click OK.

Formula Breakdown:

Formula in Cell C5:

➀ IF(B5=””,””,C5))
The IF function returns nothing if cell B5 is empty. Otherwise, returns the same value stored in C5.

➀ NOW()
The NOW function returns the current date and time.

➀ AND(B5<>””,D5<>B5)
The AND function returns TRUE if both arguments are true i.e cell B5 is not empty and cells B5 and D5 do not have the same value.

➀ IF(AND(B5<>””,D5<>B5),NOW(),IF(B5=””,””,C5))
If the AND function returns TRUE, then the IF function returns the current date and time obtained from the NOW function. Otherwise, it returns the result obtained from the argument containing the IF function.

Formula in Cell D5:

➀ ISNUMBER(D5)
The ISNUMBER function returns TRUE if cell D5 contains a number. Otherwise, it returns False.

➀ AND(ISNUMBER(D5),B5=D5)
The AND function returns TRUE if cell D5 contains a number and cells B5 and D5 have the same value. It returns FALSE otherwise.

➀ OR(C5=””,AND(ISNUMBER(D5),B5=D5))
The OR function returns TRUE if any one of the arguments is true i.e cell C5 is empty or the AND function returns TRUE. It returns FALSE if all of the arguments are false.

➀ IF(OR(C5=””,AND(ISNUMBER(D5),B5=D5)),D5,B5)
The IF function returns the same value stored in cell D5 if the OR function returns TRUE. Otherwise, it returns the value of cell B5.

➀ IF(B5=””,””,IF(OR(C5=””,AND(ISNUMBER(D5),B5=D5)),D5,B5))
The IF function returns nothing if cell B5 is empty. Otherwise, it returns the result obtained from the argument containing the IF function.

Read More: How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways)


Similar Readings


2. Apply VBA Code to Insert Timestamp in Excel When Cell Changes

You can also get a timestamp in excel when a cell changes using VBA. Follow the steps below to be able to do that.

πŸ“Œ Steps

  • First, right-click on the target worksheet tab. Then, select View Code. This will open the code module for that particular worksheet.

  • Next, copy the following code using the copy button in the upper right corner.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellCol, TimeCol, Row, Col As Integer
Dim DpRng, Rng As Range
CellCol = 2
TimeCol = 3
Row = Target.Row
Col = Target.Column
If Row <= 4 Then Exit Sub
Timestamp = Format(Now, "DD-MM-YYYY HH:MM:SS AM/PM")
If Target.Text <> "" Then
If Col = CellCol Then
Cells(Row, TimeCol) = Timestamp
Else
On Error Resume Next
Set DpRng = Target.Dependents
For Each Rng In DpRng
If Rng.Column = CellCol Then
Cells(Rng.Row, TimeCol) = Timestamp
End If
Next
End If
End If
End Sub
  • After that, paste the copied code on the blank module as shown below.

Timestamp in Excel When Cell Changes with VBA

  • Next, save the document as a macro-enabled workbook. Now, start entering data in the cells in column B. Then you will get the same results as earlier.

VBA Code Explanation:

Private Sub Worksheet_Change(ByVal Target As Range)
We will write the code inside this private subprocedure. The code will only affect the particular worksheet.

Dim CellCol, TimeCol, Row, Col As Integer
Dim DpRng, Rng As Range
Declaring necessary variables.

CellCol = 2
Data entry column.

TimeCol = 3
Timestamp column.

Row = Target.Row
Col = Target.Column
Storing the row and column numbers of the selected cell.

If Row <= 4 Then Exit Sub
Any changes within the top 4 rows won’t create a timestamp.

Timestamp = Format(Now, β€œDD-MM-YYYY HH:MM:SS AM/PM”)
The timestamp will be formatted this way. Change it as required.

If Target.Text <> β€œβ€ Then
If Col = CellCol Then
Cells(Row, TimeCol) = Timestamp
Create a timestamp if the selected cell is empty.

On Error Resume Next
Ignores any error if occurs.

Set DpRng = Target.Dependents
For Each Rng In DpRng
If Rng.Column = CellCol Then
Cells(Rng.Row, TimeCol) = Timestamp
Create timestamps for a range of cells if not empty.

Read More: How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)


Things to Remember

  • You need to use a custom format for the cells in column B to get a properly formatted timestamp.
  • The alternate formula only works when data is entered in blank cells.
  • Here, the data entry and the timestamp column are hard coded in the VBA code. You need to modify the code based on your dataset.

Conclusion

Now you know how to timestamp in excel when a cell changes. Do you have any further queries or suggestions? Please use the comment section below for that. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo