How Can I Put a Last Updated in My Excel Workbook?
Microsoft Excel does not provide an easy way to add a field to inform readers of when a file was last updated. The two time functions provided by Excel, NOW and TIME, provide information on the current time -- however, calling these functions in your file updates the cell with the current time as soon as you open the file, even if the document does not change. Using these functions with no cell protection will not give you or your users a reliable method to determine the last time the file was saved. The best solution to this problem is creating a macro for your Excel file to read the file properties that store the last time the file was saved.
Open your Excel spreadsheet and save your file as a macro-enabled Excel file. If your file is not already saved in this format, then Excel pops up a warning window. Click "No," then select "Excel Macro-Enabled Workbook" in the "Save as type" drop-down menu.
Press "Alt-F11" to open the Microsoft Visual Basic for Applications window.
Click "Insert," then "Module" in the top menu.
Copy and paste the following code into the window:Function LastSavedTimeStamp() As DateLastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")End Function
Return to your Excel spreadsheet view and click in the cell that you want to display your time stamp.
Enter "=LastSavedTimeStamp()" (without quotes) into the cell, and press "Enter."
Notice that the value may not appear in a date format. If this is the case, right-click on the cell and select "Format cells." Select "Date" in the left column, then click on the date and time format you desire. Click "Ok" to apply the formatting.
Save your file and note that the date stamp is updated only when you save the file and not when you simply open it for viewing.
Tips & Warnings
- The steps in this article apply to Excel 2013 and Excel 2010. Other versions may require different steps.
- Macro-enabled files are sometimes filtered out as possible viruses in some email programs. This is because historically some attacks on Microsoft operating systems were accomplished with macros embedded in files. Therefore, you may experience problems distributing these types of files depending on the settings of your recipient's email server.