last date saved in excel

M

MO

Hello,
I am trying to place the last date saved in an excel spreadsheet. For
instance, if I last saved a document a month ago, and I open the document
today, I want a month ago's day to print out not today's date UNLESS I SAVE
the document.

I know this can be done in MS Word through Insert Field, choosing date and
format and preserving formatiing during updates but I cannot for the life of
me figure out how to do this in Excel.

The closest I can come is header/footer and putting date in format there but
it doesn't give me the last date I saved.

Can you help?
 
G

Gary''s Student

Use this UDF:

Function lastsaved() As Double
lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

So in a cell enter:
=lastsaved() and format the cell as Date.
 
M

MO

Thank you for your reply. I don't know how to do what you're saying?? Can
you provide steps? Also will this work for already existing documents that
were saved over a year ago?
 
G

Gary''s Student

This is a User Defined Function.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx


Once the function has been installed in an existing workbook, it will
display the last saved date even if the date is quite old.
 
M

MO

Gary's Student--you're brilliant. I honestly don't know what I'm doing but I
need to ask you a few more questions. I don't want this for all files, is
there a way to just do certain ones? Where do I star with the UDF, in any
OPEN file or just from the blank Microsoft Excel Screen. Sorry, I will try
not to ask any more questions.

I truly appreciate your patience.
 
M

MO

Never mind my last post....IT WORKS AND YOU ARE THE BEST :)

Thank you, thank you, thank you!!!!!
 
M

MO

I thought it was working fine. When I enter =lastsaved() and format the cell
as Date, I get #VALUE!. Any suggestions?
 
G

Gary''s Student

It means that you have not yet saved the file!!!

(sorry, I should have mentioned this)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top