How do I display the most recent "modified" date in Excel sheets?

[Guest]

Can anyone advise me how to add a formula to an excel worksheet that will
display the most recent "modified" date? Please note I am familiar with NOW
and TODAY() functions - instead, I am interested in displaying only the date
the file was last saved. The NOW and TODAY() functions update every time the
file is opened and that is not what I want.

 

[Guest]

I don't know that there's a worksheet function to provide that information,
but you can create your own! Called a 'User Defined Function', or UDF.

Public Function GetModifiedDate() As Date
Dim fs, f
Application.Volatile
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(ThisWorkbook.FullName)
GetModifiedDate = f.DateLastModified
Set f = Nothing
Set fs = Nothing
End Function

To use this, open the workbook and press [Alt]+[F11] and then choose Insert
| Module in the VB Editor window. copy and paste the above code into the
code area and close the window.
Next, in the cell where you want the last modifed date to show up put this
formula:
=GetModifiedDate()

NOTE: in order for this to work, the workbook must have been saved to disk
at least once.

ANOTHER NOTE: this may not automatically update. The Application.Volatile
statement tells it to update whenever any new calculation takes place on that
worksheet, but if no new calculations take place, then it won't update
either. You may have to press [F9] to get the latest date for the file.

 

[Guest]

Probably a much better way!

Public Function GetModifiedDate() As Date
Application.Volatile
GetModifiedDate = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

do same as before - copy it into the workbook (delete previous function
w/same name if you already copied it into the workbook), then set up the
formula in the cell where you want the data/time of last modification to show
up.

 

[Chip Pearson]

Try the following code:

Public Function LastSaved(Optional FileName As String) As Variant
If FileName = vbNullString Then
If ThisWorkbook.Path = vbNullString Then
LastSaved = CVErr(xlErrValue)
Else
LastSaved = FileDateTime(ThisWorkbook.FullName)
End If
Else
If Dir(FileName, vbNormal) = vbNullString Then
LastSaved = CVErr(xlErrValue)
Else
LastSaved = FileDateTime(FileName)
End If
End If
End Function


You can call it from a cell with =LastSaved()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thanks, Chip. I'll give this a shot.
--
CC

Try the following code:

Public Function LastSaved(Optional FileName As String) As Variant
If FileName = vbNullString Then
If ThisWorkbook.Path = vbNullString Then
LastSaved = CVErr(xlErrValue)
Else
LastSaved = FileDateTime(ThisWorkbook.FullName)
End If
Else
If Dir(FileName, vbNormal) = vbNullString Then
LastSaved = CVErr(xlErrValue)
Else
LastSaved = FileDateTime(FileName)
End If
End If
End Function


You can call it from a cell with =LastSaved()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thanks, I'll give this a try!
--
CC

Probably a much better way!

Public Function GetModifiedDate() As Date
Application.Volatile
GetModifiedDate = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

do same as before - copy it into the workbook (delete previous function
w/same name if you already copied it into the workbook), then set up the
formula in the cell where you want the data/time of last modification to show
up.