Menu
Does anyone know how to display the Last Saved Date of an Excel Spreadsheet on one of the worksheets?
I have found ways to do it using macros, but the spreadsheet is populated by an add-in called Jet Essentials, and this does not like macros so a solution here must not use one.
442
To find the last saved date of an Excel file, in the Ribbon, go to File > Info. In the Info window, on the right side, you can find the Last Modified (in this example 6/5/2015). This is the date and time when the file was last saved.
Question: In Microsoft Excel, how can I autoupdate a date on an Excel worksheet to the Last Modified Date? Answer: There is no built-in function in Excel that will return the last modified date of an Excel Workbook (ie: Excel file). However, you can create a Macro function that will return this value.
Insert Date and Timestamp Using NOW FunctionRight-click on the cell and select 'Format cells'. In the Format Cells dialog box, select 'Custom' category in the Number tab. In the Type field, enter dd-mm-yyyy hh:mm:ss. Click OK.
To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.
Function LastSavedTimeStamp() As Date LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") End Function Save the module, close the editor and return to the worksheet. Click in the Cell where the date is to be displayed and enter the following formula:
You have to follow the following steps. Firstly, press ALT+F11 or you have to go to the tab Developer, select Visual Basic to open Visual Basic Editor, and click Insert. Select Module. Sub Last_Saved_Dates_1 () Range ("C4").Value = Format (ThisWorkbook.BuiltinDocumentProperties ("Last Save Time"), "short date") End Sub
One approach: If you're familiar with VBA you can add this code to a Module in the workbook to create a user-defined function: Format a cell with Date formatting of your choice & enter the function as =LastSaved () . No arguments are required between the parens. It will update each time the workbook is opened.
Need to format the cell where you put the “=LastSavedTimeStamp ()” command. If so, right-click on that cell and select “Format Cells…”; click the “Number” tab and select “Date”; then under “Type” select how you want it formatted. Click “OK.” Above directions were written using Excel 2010.
thought I would update on this.
Found out that adding to the VB Module behind the spreadsheet does not actually register as a Macro.
So here is the solution:
Code
Function LastSavedTimeStamp() As Date
LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function
Code
=LastSavedTimeStamp()
This might be an alternative solution. Paste the following code into the new module:
Public Function ModDate()
ModDate =
Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")
End Function
Before saving your module, make sure to save your Excel file as Excel Macro-Enabled Workbook.
Paste the following code into the cell where you want to display the last modification time:
=ModDate()
I'd also like to recommend an alternative to Excel allowing you to add creation and last modification time easily. Feel free to check on RowShare and this article I wrote: https://www.rowshare.com/blog/en/2018/01/10/Displaying-Last-Modification-Time-in-Excel
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
