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))`

- 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))`

- 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.

- 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**

**How to Insert Static Date in Excel (4 Simple Methods)****Excel VBA: Insert Timestamp When a Macro Is Run****How to Insert Excel Date Stamp When Cells in Row Are Modified****Convert Unix Timestamp to Date in Excel (3 Methods)**

### 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.

- 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.