Display Modified Date of Excel File

In my last post about how to append or prepend text to data in a cell, Larry asked how one might be able to display the modified date of an excel file. This tutorial will explain the most efficient way to display the modified date of the file you are working on in a cell.

Get Ready For Macros

Getting right to it, the modified date of an excel file exists, but it isn’t accessible via any of the functions that Excel has created for us. Instead, we’ll create our own function in the Visual Basic side of Excel so we can gather the information we need and then put it in a cell just as if it were a typical function.

Follow these steps:

1. Open a file that is macro enabled. The use of macros is disabled by default. If you have a file open already, you’ll need to do a file > save as and choose to save it as a macro enabled file.
2. Hit CTRL + F11 ALT+F11 (Edit thanks to FLoffice in comments). This will take you to the Visual Basic side of the excel file. I promise, this place isn’t as scare as it looks.
3. At the top, choose Insert > Module. A new text window called Module1 will pop up.
4. Enter the following code in the module and save the file.Public Function GetModifiedDate() As Date
Application.Volatile
GetModifiedDate = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function
5. You just created a workable function called GetModifiedDate(). You can use this just like any other function. In fact, excel will even auto-complete as if you were typing in any of the other functions. Give it a try, head back to the excel file, click on a cell, and type in “=GetModifiedDate()” and hit enter.
6. You’ll see some weird numbers. This was discussed in my append\prepend post, but basically you’ll just need to change the formatting to date and you’ll be set.

Let me know how it goes. Cheers!