It has already been mentioned many times about the different and useful functions that the Microsoft Excel spreadsheet has. Which you can even find online and even if you are one of the millions of users in the world who use it, you did not know that the modification of a cell can be registered.
It is for this reason that we have decided to present an article that will explain step by step how to register date and time of modification of data or cell records in Excel.
You may not know that Excel databases are frequently updated by different users. For this reason, it is necessary to keep track of the different modifications or changes that are made to the data in the Excel cells. And so in this way, we can have knowledge of the last update that was made of a record.
In the following tutorial we will explain how to record date and time of modification of data or cell records in Excel. In a very simple way, just as we did in the article that I explain how to create an index in Excel using forms. So we ask you, do not stop reading this interesting guide, which as always will be very useful.
How to record date and time of modification of data or cell records in Excel
So that you can register the date and time of modification of data or cell records in Excel, we are going to make use of the Worksheet_Change event. With it, we can execute a VBA code at any time when a cell in our spreadsheet is modified. And in this way we can register in our database the changes that are made.
Suppose we have a table that shows four columns, in them we have in column A the name, in column B the surname, in column C the telephone. And we will have in column D the last update, with X number of cells. In this column D, I want to enter the record of the modified date and time in any of the cells.
No matter which column you modify, column D will automatically display the date and time of the last change that was recorded. To perform the operation of recording date and time of modification of data or cell records in Excel. It is to go to the VBA editor, to create a code or create a macro, for this we go to the top and click on the Developer tab, then on VBA.
Enter VBA code to register date and time
Now, on the left side we are going to position ourselves on the sheet that contains our table, we make a right clip and in the options that appear, we must select or click View code. Now on the right side we must enter the following code:
Private sub worksheet_Change(ByVal Target As Range)
It Target.Column < 4 Then
Cells(Target.Row, 4). Value = Now
End If
End Sub
When this event has been fired, we will compare the cell of the column that has been modified, by means of the Target property. Column.
If the parameters in said column are less than 4, the current time and date will be inserted through the Now function. You should notice that both the time and the date are inserted in column 4 and in the cell that has been changed and we will obtain this with the Target.Row property.
The next step that we are going to carry out is to verify if this code works, for this we are going to minimize this window. So let’s go to the sheet where our table is located and make some modification in any cell and column. When doing this, the modification date and time should appear in column D and in the same row where the change originated.
If, on the other hand, you enter a new name in the box, column D of that same row will show the time and date that you are making the entry. And in this very simple way, using VBA, we have been able to learn a new Excel function. Where you can record data modification date and time or cell records in Excel.