Formula that displays a Modified Date

[raisorpr]

Is there a formula that will display the documents "Modified Date" is a cell?



Thanks,
raisorpr.

 

[Bob Phillips]

A UDF

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

Not "modified date" but you can have last save date/time entered in a cell.

Basically the same thing because a "save" is a modification.

You will need a User Defined Function.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Enter in a cell one of the below...............

'=DOCPROPS("author")
'or
'=DOCPROPS("last save time")
'or
'DOCPROPS("creation date")

For a list of all built-in Document Proerties, run this macro after copying the
DocProps function to a general module in your workbook.

Paste the macro into the same module.

Sub documentprops()
'list of properties on a new sheet
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 4).Value = "=DocProps(" & "A" & rw & ")"
rw = rw + 1
Next
End Sub


Gord Dibben MS Excel MVP

 

[raisorpr]

I tried coping and pasting this code but didn't work.

Not sure what I did wrong. I was trying to get the modified date to display
in Cell A1 of Sheet1.

 

[raisorpr]

I tried coping and pasting this code but didn't work.

Not sure what I did wrong. I was trying to get the modified date to display
in Cell A1 of Sheet1.

 

[Gord Dibben]

"Didn't work" means what?

Nothing? Error message?

To where did you copy and paste the code?

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook(project) and Insert>Module.

Copy/paste the DocProps UDF into that module.

Alt + q to return to the Excel window.

In A1 enter =DocProps("last save time")

Format as Date


Gord