ADD A LAST UPDATE CELL TO A SPREAD SHEET

[DarbyBrown]

I am having trouble adding a ‘Last Update’ cell to my worksheet. The things I have tried are:

First, Used the VS editor and created a module, (Alt-F11):
Function ModDate()
ModDate = Format(FileDateTime(“MyFileName.xlsm”))
‘With and without the quote marks’
End Function

Then called the function in the cell formula
=ModDate()
The cell displays #VALUE!

Second, I tried to put the Formula directly in the cell:

=Format(FileDateTime(“MyFileName.xlsm”, “m/d/yy”))
The cell displays “$NAME?

Then, I changed the Module formula to:

ModDate = FileDateTime(“MyFileName.xlsm”)
(I also tried this formula directly in the cell)

The cell displays $VALUE!

What am I doing wrong???

 

[Yongle]

requires the full path
So for the workbook containing the formula ...

Function ModDate()
    ModDate = Format(FileDateTime(ThisWorkbook.path & "\" & ThisWorkbook.Name))
End Function

To find date for a different workbook

    ModDate = Format(FileDateTime("D:\folder\ path\ to other workbook" & "\" & "MyFileName.xlsm"))

 

[DarbyBrown]

Yongle, worked great, thanks.

 

[Yongle]

thanks for your feedback

 

[DarbyBrown]

thanks for your feedback

I spoke a bit too soon, the formula did work as I needed it to except for one thing. I may go into the Workbook but not make any changes to the data, so I don't want the Last Update to change. With this formula the date changes every time I open the workbook. I only want it to update when I make any change to the data. Is this possible?

 

[Yongle]

perhaps
.. do not save the file
... open it read only