This tutorial will demonstrate how to insert the last modified date and time in an excel cell. When inserting lots of data entries, it is very important to make modifications properly. But to avoid confusion, we also need to insert the last modified date and time. By that, we can track the changes and know when they are made which eases our work. So, it is essential to learn to insert the last modified date and time in excel.
How to Insert Last Modified Date and Time in Excel Cell: 3 Easy Methods
We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have the Input in column B and the Timestamp in column C. If you follow the steps correctly, you should learn how to insert the last modified date and time in the excel cell on your own. The steps are.
1. Using File Tab
At the very beginning, our goal is to insert the last modified date and time in an excel cell using the File Tab. The steps of this method are.
Steps:
- To begin with, go to the File tab.
- In addition, select the Info option.
- Finally, you will get the desired result.
Read More: How to Insert Timestamp in Excel When Cell Changes
2. Using VBA Code
Now, we want to insert the last modified date and time in an excel cell by using the VBA code. We can fulfill our goal in two ways. The steps of the methods are described below.
2.1. Creating User-Defined Function
Our aim is to use VBA code to create the User-Defined Function in excel. And with this, we will insert the last modified date and time in the excel cell. The steps of this method are.
Steps:
- First, press the Alt+F11 options to open the VBA window and select the Module option from the Insert tab.
- Next, insert the following code in the window.
Function AutoTimestamp(xRef As Range)
If xRef.Value <> "" Then
AutoTimestamp = Format(Now, "hh:mm:ss AM/PM mm-dd-yyyy")
Else
AutoTimestamp = ""
End If
End Function
- Finally, you will get the desired result.
2.2. Using Command Button
In the following case, we will try to insert the last modified date and time in the excel cell by using the Command Button. The steps of this method are.
Steps:
- To begin with, go to Developer > Insert > ActiveX Controls options.
- In addition, select the cell range and insert the CommandButton1 option.
- Furthermore, right-click on the button and select the View Code option.
- Afterward, the VBA window will come on the screen. Then, select the Module option from the Insert tab and insert the following code in the window.
Private Sub CommandButton1_Click()
Range("C12").Value = Now()
End Sub
- Next, if you want to change the name of the button, you can again right-click on the button and select the Properties option.
- Therefore, make the desired change in the Properties dialog box.
- Moreover, you will have the desired result.
- After that, remove the Design Mode option in the Developer tab.
- Then, connect the button with cell C12.
- Finally, you will get the desired result.
Read More: How to Insert Excel Timestamp When Cell Changes Without VBA
3. Utilizing IF Function
We will insert the last modified date and time in the excel cell by utilizing the IF Function. The steps of this method are.
Steps:
- First, insert the following formula in cell C5.
=IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")
- Second, you will result in this cell.
- Then, use the Fill Handle option to apply the formula to all desired cells.
- Third, if you put inputs in column B, then you will get the results in column C.
- Last, you will get the desired result.
🔎 How Does the Formula Work?
- ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())): This portion represents the reference cells.
- NOW(),IF(CELL(“address”)<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())): this portion represents the selected range of the cell with conditions.
- IF(AND(C5<>””, CELL(“address”)=ADDRESS(ROW(B5), COLUMN(B5))): This portion represents the conditions within the defined reference cells.
Read More: How to Add Date and Time in Excel When Printing
How to Use Date Formula in Excel
Our aim is to insert dates using the DATE Function. The steps of this method are.
Steps:
- First, insert the following formula in cell B5.
=DATE(1999,1,1)
- After pressing the Enter button, you will get the result for this cell.
- After that, if you insert the desired dates individually in column B then you will get the results in column C.
- Finally, you will get the desired result.
Read More: How to Insert the Current Date and Time in Cell A1
Things to Remember
- In the case of the IF Function, if you are a Microsoft 365 user, you only have to press the Enter button to run the formula. Otherwise, you have to press the Ctrl+Shift+Enter buttons together.
- For using VBA code, the files must be saved as Excel-Macro Enabled Workbook. Otherwise, codes won’t work.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to insert the last modified date and time in the excel cell. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
