How to Insert Last Modified Date and Time in Excel Cell

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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 Insert Timestamp in Excel When Cell Changes


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.

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.


Related Articles


<< Go Back to Timestamp | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo