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.
Step 1
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.
Video of the Day
Step 2
Press "Alt-F11" to open the Microsoft Visual Basic for Applications window.
Step 3
Click "Insert," then "Module" in the top menu.
Step 4
Copy and paste the following code into the window:
Function LastSavedTimeStamp() As Date LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") End Function
Step 5
Return to your Excel spreadsheet view and click in the cell that you want to display your time stamp.
Step 6
Enter "=LastSavedTimeStamp()" (without quotes) into the cell, and press "Enter."
Step 7
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.
Step 8
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.
Video of the Day