Often you may want to display the date that an Excel file was last saved.
The easiest way to do this is by writing some code in VBA in Excel.
This might seem intimidating if you’re not familiar with VBA but the process is straightforward and the following step-by-step example shows exactly how to do so.
Step 1: Show the Developer Tab in Excel
First, we need to make sure the Developer tab is visible on the top ribbon in Excel.
To do so, click the File tab, then click Options, then click Customize Ribbon.
Under the section called Main Tabs, check the box next to Developer, then click OK:
Step 2: Create a Macro Using VBA
Next, click the Developer tab along the top ribbon and then click the Visual Basic icon:
Next, click the Insert tab and then click Module from the dropdown menu:
Next, paste the following code into the module code editor:
Function LastSavedDate() As Date
LastSavedDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function
The following screenshot shows how to do so:
Next, close the VB Editor.
Step 3: Use the Macro to Display the Last Saved Date
Lastly, we can use the macro we created to display the last saved date for the workbook.
To do so, type the following formula into any cell you’d like:
=LastSavedDate()
We’ll type this formula into cell A1:
By default, the function returns a numeric value.
To instead display a date, click cell A1 and then click the Number Format dropdown menu from the Home tab, then click Short Date:
The value in cell A1 will now be displayed as a date:
We can see that this particular Excel workbook was last saved on 11/20/2023.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Remove Date Format in Excel
How to Use VLOOKUP With Date Range in Excel
How to Extract Minutes from Datetime in Excel
Well written and presented tutorial –> easy to follow!
Thank you. Thank you. Thank you.
You are very welcome John! We appreciate your feedback and support!
Easy-Peasy with your tutorial.
Thank You!
Thank you, David for the feedback!
Thank you. I did it a few days ago and it worked perfectly. But the next date, the date in the cell has not been updated as expected. What’s wrong?
Great question! It sounds like you previously used a method to insert the **last saved date** in Excel, and it worked at first but then stopped updating.
Here’s why that can happen and how to fix it:
—
### ✅ What You Likely Did:
You probably used this formula:
“`excel
=TEXT(NOW(),”mm/dd/yyyy hh:mm:ss”)
“`
Or used **VBA** to insert the **last saved timestamp**.
—
### ❌ Why It’s Not Updating:
– **NOW() and TODAY() only recalculate when the sheet recalculates** (e.g., when you enter something or press F9). They **do not auto-update just from saving**.
– If you **used VBA**, the macro might not be running anymore due to:
– Macros being disabled
– The VBA code not being in the correct workbook/module
– The `Workbook_BeforeSave` event not triggering
—
### ✅ How to Properly Show “Last Saved Date” That Auto-Updates:
#### 🔧 Option 1: Use VBA to Update a Cell on Save
Here’s a simple VBA script:
1. Press `Alt + F11` to open the VBA editor.
2. In the **ThisWorkbook** section, paste this:
“`vba
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets(“Sheet1”).Range(“A1”).Value = “Last saved: ” & Now
End Sub
“`
– Make sure:
– Replace `”Sheet1″` with your actual sheet name.
– Replace `”A1″` with your desired cell.
3. Save the workbook as a **macro-enabled workbook** (`.xlsm`).
—
### 🔄 How to Test It:
– Save the workbook (Ctrl + S).
– Check the cell you assigned. It should now update with the correct time.
—