Displaying Last modified date and time of file in a cell

**modest_16081982** · 07-11-2009, 06:04 AM

How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.

This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".

**JBeaucaire** · 07-11-2009, 06:41 AM

How to use the macro:

1. Open up your workbook
2. Right-click the Excel icon to the left of FILE Menu
3. Select VIEW CODE
4. Copy and Paste in your code (given below) in the ThisWorkbook module that appears
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. When you save your sheet, the current time stamp will be updated in cell E1.

Please Login or Register  to view this content.

**modest_16081982** · 07-14-2009, 05:12 AM

So I tried the above code, but it does not work. I don't get any time stamp when I close and re-open the file. The cell E1 is blank.

I am using Excel 2002 version. I am attaching a sample file with the VBA code in it (Sheet1).

**martindwilson** · 07-14-2009, 05:35 AM

code needs to go in workbook module not work sheet module

**modest_16081982** · 07-14-2009, 12:08 PM

Originally Posted by martindwilson

code needs to go in workbook module not work sheet module

That worked just fine....

**modest_16081982** · 07-14-2009, 12:17 PM

Sorry I take my words back. It didn't quite do what I was hoping for it.

The current code updates the time stamp even if I don't modify the file. Looks like it is showing the last access/open time instead of the last modified date.

I am looking for the last modified time as mentioned in my first post.

**shg** · 07-14-2009, 12:29 PM

Move the code to the BeforeSave event (still goes in the ThisWorkbook module).

Please Login or Register  to view this content.

**JBeaucaire** · 07-14-2009, 01:24 PM

Edit...deleted as unnecessary.

**danblee** · 12-20-2012, 03:51 PM

I stumbled upon this thread not too long ago and it pointed me in the right direction, but I'm not sure it completely answered the question. Basically, you can create your own function using the Visual Basic side of Excel. From there, you can call on the function in any cell you'd like and it will display the modified date of the file. Here's the function:

Please Login or Register  to view this content.

I made a quick post about this in my blog with instructions on how to add it, for those of you that may need a full tutorial.

http://danblee.com/display-modified-date-of-excel-file/

Cheers!