Return Date and Time on save

[Tom Richards]

When opening up a file, and modifying the spreadsheet, I need a formula in a
cell that contains the time and date. When the spreadsheet is modified, the
date and time is updated, not each time the file is opened.

I've researched this and found solutions that I don't know how to
implement - One solution
involved creating a vbe file (I'm an Excel newbie and have no clue what this
is) and other
solutions were just as foreign to me. So step-by-step advice would be much
appreciated!

Thanks,
Tom

 

[Frank Kabel]

Hi
use the following 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
=DOCPROPS("last save time")
(format cell as date)

for more about UDFs see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tom Richards]

Thanks Frank but I haven't had luck with this. I read and re-read that UDF
help page. I took the code

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 saved it as personal.xls in my XLStart folder. Then I opened the
worksheet I want to use the time and date function in, placed

=DOCPROPS("last save time")

in a cell formatted as DATE and it throws the error

#NAME

Thanks for your help.

Tom

 

[Frank Kabel]

Hi
if you place it in your personal.xls file you'll have to reference this
workbook in your formula: So try
=personal.xls!DOCPROPS("last save time")

 

[Tom Richards]

Thanks Frank. This worked!

 

[Guest]

Hi guys! I'm looking to do this but only in the footer of a doc -- any way
to do it there?

 

[Frank Kabel]

Hi
you have to put this information also via VBA in the footer. No chance
to do it directly via the dialog