How to Insert Last Modified Date and Time in Excel Cell

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.


Download Practice Workbook

You can download the practice workbook from here.


3 Easy Methods to Insert Last Modified Date and Time in Excel Cell

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.

insert last modified date and time in excel cell


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 Excel Timestamp When Cell Changes Without VBA (3 Ways)


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.

Opening VBA Window to Insert Last Modified Date and Time in Excel Cell

  • 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

Inserting VBA code to Insert Last Modified Date and Time in Excel Cell

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

Creating Command Button to Insert Last Modified Date and Time in Excel Cell

  • In addition, select the cell range and insert the CommandButton1 option.

  • Furthermore, right-click on the button and select the View Code option.

Editing Command button Name to Insert Last Modified Date and Time in Excel Cell

  • 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

Inseting VBA Code to Insert Last Modified Date and Time in Excel Cell

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

Using Properties Box to Insert Last Modified Date and Time in Excel Cell

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

Connecting Button to Cell to Insert Last Modified Date and Time in Excel Cell

  • Finally, you will get the desired result.

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


Similar Readings


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())),"")

Insert Last Modified Date and Time in Excel Cell

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

Insert Last Modified Date and Time in Excel Cell

🔎 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 Insert Timestamp in Excel When Cell Changes (2 Effective Ways)


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 Static Date in Excel (4 Simple Methods)


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.

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. Follow the ExcelDemy website for more articles like this. 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.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo