This tutorial will demonstrate how to insert an Excel date stamp when cells in a row are modified. Sometimes we need to track the activities of a specific task or employee. We can do this by automatically updating date stamps with the modification of cells. Along with the use of Excel formulas, we will also use VBA code in this article.
Download Practice Workbook
You can download the practice workbook from here.
4 Quick Tricks to Insert Excel Date Stamp When Cells in Row Are Modified
Throughout this article, we will demonstrate 4 quick tricks to insert an Excel date stamp when cells in a row are modified. To illustrate the methods of this article we will use the following dataset. In the following dataset, we can see two columns. The first column takes the entry of employee names. After the entry in the first column, we will get the date of entry automatically in the second column. We will use the same dataset to explain all the methods.
1. Use of Circular Reference Tricks to Insert Excel Date Stamp
First and foremost, we will use circular reference tricks to add an Excel date stamp when cells in a row are modified. In this method, we will create an Excel formula with multiple functions. Follow the below steps to perform this method.
- To begin with, go to the File tab.
- In addition, Select Options from the menu.
- A new dialogue box named Excel Options will appear.
- Furthermore, select the Formulas option.
- Then, check the option ‘Enable iterative calculation’ from the Calculation options section.
- Now, click on OK.
- After that, select cell C5.
- Type the following formula in that cell:
- Press Enter if you are using Microsoft Excel 365. Otherwise, you have to press Ctrl + Shift + Enter since it is an array formula.
- Subsequently, drag the Fill Handle tool from cell C5 to C8. It will copy the above formula in all the selected cells.
- Moreover, select cells C5 to C8.
- Then, go to the Home tab. Click on the Number Format drop-down, and select the option Short Date.
- Now, enter the name of employees in the first column.
- As a result, we get the date of entry automatically like in the following image.
🔎 How Does the Formula Work?
- CELL(“address”): Here the CELL function addresses the last edited cell.
- ROW(B5): The ROW function returns the row number of cell B5.
- COLUMN(B5): The COLUMN function returns the column number of cell B5.
- NOW(): The NOW function returns the exact time of data entry.
- ADDRESS(ROW(B5),COLUMN(B5)): The ADDRESS function creates the cell reference B5 as text.
- IF(CELL(“address”)<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW()): Here, the IF function returns the value of cell C5, if the return value by the CELL function and ADDRESS function is not the same. Otherwise, it will return the result of the NOW function.
- AND(C5<>””,CELL(“address”)=ADDRESS(ROW(B5),COLUMN(B5))): The AND function combines two logic.
- IF(AND(C5<>””,CELL(“address”)=ADDRESS(ROW(B5),COLUMN(B5))),NOW(): If both logics are true this part will return the date of entry.
- IF(B5<>””,IF(AND(C5<>””,CELL(“address”)=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL(“address”)<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),””): If cell B5 is blank the formula will return nothing. But, if we insert any text there the above formula will return the date stamp of that entry.
2. Combine IF & TODAY Functions to Add Date Stamp in Excel
In the second method, we will use the IF function and the TODAY function to add an Excel date stamp. The date stamp will update automatically when cells in a row are modified. To apply the combination of IF & TODAY functions follow the below steps.
- In the beginning, select cell C5.
- Additionally, insert the following formula in that cell:
- Hit Enter.
- Then, drag the Fill Handle tool from cell C5 to C8.
- In the end, enter the names of employees in the first column. So, we get their entry time as well. If we update the names two days later then the date will update automatically.
- How to Convert Unix Timestamp to Date in Excel (3 Methods)
- Convert Active Directory Timestamp to Date in Excel (4 Methods)
- How to Convert SAP Timestamp to Date in Excel (4 Ways)
3. Apply VBA to Insert Date Stamp When Cells in Row Are Modified
Suppose you are an advanced Excel user and Familiar with VBA. There is another way to add an Excel date stamp with VBA. We will use the following dataset to illustrate this method. Let’s follow the below steps to execute this method.
- First, right-click on the worksheet tab named VBA and select the option View Code.
- The above command will open a VBA code window.
- Next, type the following code in that blank VBA code window:
Private Sub Worksheet_Change(ByVal Target As Range) Dim WRng As Range Dim rg As Range Dim zOffsetColumn As Integer Set WRng = Intersect(Application.ActiveSheet.Range("B:B"), Target) zOffsetColumn = 1 If Not WRng Is Nothing Then Application.EnableEvents = False For Each rg In WRng If Not VBA.IsEmpty(rg.Value) Then rg.Offset(0, zOffsetColumn).Value = Now rg.Offset(0, zOffsetColumn).NumberFormat = "dd-mm-yyyy" Else rg.Offset(0, zOffsetColumn).ClearContents End If Next Application.EnableEvents = True End If End Sub
- Then, click on the Run button or, press the F5 key to run the code.
- Moreover, create a macro named VBA1 and click on the Run button.
- After that, enter the following employee’s name in the first column.
- As a result, we automatically get the date stamp of entry time like the following image.
4. Add Date Stamp in Excel with Custom Function for Modified Cells in Row
This method is another use of VBA code to add an excel data stamp. In this method, we will create a custom function with VBA code. That function will allow us to enter a timestamp of any entry in our worksheet. We will use the following data set to illustrate this method. We will make entries in column B. The code will return a corresponding timestamp in column C. Go through the following steps to do this.
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic from the ribbon.
- A VBA project window will open.
- Thirdly, right-click on Sheet5 (Custom Function). You will select the worksheet where you want to apply the code.
- Then, select Insert > Module.
- A blank VBA code window will open.
- Furthermore, type the following code in that code window:
Function TIMESTAMP(Ref As Range) If Ref.Value <> "" Then TIMESTAMP = Format(Now, "dd-mm-yyyy") Else TIMESTAMP = "" End If End Function
- Now, click on the Save button to save the code.
- Afterward, start typing the name of the custom function in cell C5. We get a suggestion for our new custom function.
- Moreover, select the TIMESTAMP function.
- Insert the formula with the newly created function in that cell:
- Press Enter.
- Then, drag the Fill Handle tool from cell C5 to C7.
- Lastly, type the names of employees. We automatically get their entry date. Suppose, we will update the names after two days. Then, the date stamp will update automatically with the modification of cells in the row.
In conclusion, this tutorial shows 4 quick tricks to add an excel date stamp when cells in a row are modified. Download the sample worksheet given in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to reply to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.