How to Insert Last Modified Date and Time in Excel Cell

We’ll use a sample dataset, with Input in column B and the Timestamp in column C. We’ll input the timestamp in column C whenever we modify the respective cell in Input.

insert last modified date and time in excel cell


Method 1 – Using the File Tab to Get the Last Modified Date and Time of the File

Steps:

  • Go to the File tab.

  • Select the Info option.

  • You will get the desired result.

Read More: How to Insert Excel Timestamp When Cell Changes Without VBA


Method 2 – Using VBA Code


Case 2.1 – Creating a User-Defined Function

Steps:

  • Press Alt + F11 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

  • 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

  • You will get the desired result.


Case 2.2 – Using a Command Button

Steps:

  • Go to Developer, then to Insert, and pick the first option in the ActiveX Controls group.

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

  • Select the cell range and insert the CommandButton1.

  • 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

  • The VBA window will pop up. 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

  • If you want to change the name of the button, you can right-click on the button and select the Properties option.

  • Make the desired change in the Properties dialog box.

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

  • Here’s the button.

  • Uncheck the Design Mode option in the Developer tab.

  • Connect the button with cell C12.

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

  • You will get the desired result.

Read More: How to Insert Timestamp in Excel When Cell Changes


Method 3 – Utilizing the IF Function

To use the iterative formula you need to do the following changes in Excel options

Enable Iterative Calculations:

First, go to File > Options > Formulas.
Then, select Enable iterative calculation.


Steps:

  • 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

  • Hit Enter and use the Fill Handle option to apply the formula to all desired cells.

  • If you put values in column B, then you will get the results in column C.

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 the Date Formula in Excel

Steps:

  • Insert the following formula in cell B5.
=DATE(1999,1,1)

  • After pressing the Enter button, you will get the result for this cell.

  • If you insert the desired dates individually in column B, you will get the results in column C.


Things to Remember

  • For 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.
  • When using VBA code, the files must be saved as an Excel-Macro Enabled Workbook.

Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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

2 Comments
  1. Hi, Using Method 3 (IF function), I get a circular reference error and nothing in column C.

    • Hello Patrick Raimond,

      To use the iterative formula you need to do the following changes in Excel options

      Enable Iterative Calculations:

      First, go to File > Options > Formulas.
      Then, select Enable iterative calculation.

      Today’s Output:

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo