How to Insert Excel Date Stamp When Cells in Row Are Modified

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.

4 Quick Tricks to Insert Excel Date Stamp When Cells in Row Are Modified


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.

STEPS:

  • To begin with, go to the File tab.

Use of Circular Reference Tricks to Insert Excel Date Stamp

  • In addition, Select Options from the menu.

Use of Circular Reference Tricks to Insert Excel Date Stamp

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

Use of Circular Reference Tricks to Insert Excel Date Stamp

  • After that, select cell C5.
  • Type the following formula in that cell:
=IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")

Use of Circular Reference Tricks to Insert Excel Date Stamp

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

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


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.

STEPS:

  • In the beginning, select cell C5.
  • Additionally, insert the following formula in that cell:
=IF(B5="","",TODAY())
  • Hit Enter.
  • Then, drag the Fill Handle tool from cell C5 to C8.

Combine IF & TODAY Functions to Add Date Stamp in Excel

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

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


Similar Readings


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.

STEPS:

  • First, right-click on the worksheet tab named VBA and select the option View Code.

Apply VBA to Insert Date Stamp When Cells in Row Are Modified

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

Apply VBA to Insert Date Stamp When Cells in Row Are Modified

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

Read More: Excel VBA: Insert Timestamp When a Macro Is Run


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.

STEPS:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic from the ribbon.

Add Date Stamp in Excel with Custom Function

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

Add Date Stamp in Excel with Custom Function

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

Add Date Stamp in Excel with Custom Function

  • 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:
=TIMESTAMP(B5)
  • 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.

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


Conclusion

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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

4 Comments
  1. I was able to make Option 2 work great, except in a table with sortable header. The formula duplicates down the entire column, without unique row based formulas. So, I changed the “B5” to “2” (representing row 2) and it is only row 2 down the entire column. If I change row 3 formula to “3”, it changes on every row to “3”. Is there a way to do the formula in a table?
    Formula used: =IF(2=””,””,TODAY())

    • Hi Kala,
      Thanks for your question. According to your comment, you want to work with a table that’s why I have created the following table.
      dataset
      You can use the following formula
      =IF(ROW()=””,””,TODAY())
      Here, ROW() will return the corresponding row number for a row; like for Row 5, it will give the value 5, for Row 6 you will have 6.
      formula
      Then, you can insert the names in the Name column.
      result

  2. Hey!

    How would I make this work if I wanted to track modifications to any cell within the row? For example, instead of just tracking changes made to column B with a timestamp in column C, I’d like to track if a change is made in EITHER column A or column B with a timestamp in column C.

    • Hi, Megan!
      Thank you for your query.
      You can definitely track change for multiple columns. If you want to track change for the A and B columns for row 5, then you can apply the following formula.
      =IF(AND(A5="",B5=""),"",TODAY())
      Then you can use the fill handle feature for all the other rows.

      Regards,
      Tanjim Reza

Leave a reply

ExcelDemy
Logo