How would I get a cell within excel to contain the current time and date when an edit of the document is made?
So, if I open the excel file, make a change somewhere, and close the file, the cell would contain the computer’s system date and time of when the most recent edit was made of the file.
I am producing a sort of database for work which multiple employees will need to edit on a regular basis. I don’t want to have to rely on them to update a “date of last edit” cell every time, as I am sure they would forget 80% of the time (and so would I as it happens)
I could not find an easy way to do this using the date/time functions in the program.
It would be easy (sorta) to do with macros. You could set up a cell with the “now” or “today” function. Then write a macro such that this value is copied in as a number into a protected cell.
You could put a button on the screen and make it easy for people to use, but they would have to push this button before exiting.
Another option requiring cooperation is the changes editor under “Tools.”
Maybe some of the deeper users can suggest a fancy auto-save macro.
Lobsang,
Try this:
Cite
How it’s done.
How can I link the modified date of a file(last date file was saved)to a cell in the worksheet?
You can use a function. Paste the code in a module in
your workbook.
Function LModDate()
LModDate = ActiveWorkbook.BuiltinDocumentProperties(11)
End Function
The function will be available using
the function wizard in User Defined. Or once the
function is in your workbook paste the following into
the cell you need. Be sure to use a date format with
the cell.
=LModDate()
Brilliant! That worked. thanks.
wow, that’s a first. I posted it but can’t seem to make it work for me. :smack: haha.
I can describe exactly how I did it if you want… 
Tools->Macro->Visual Basic Editor (or alt-F11)
In the visual basic editor go to Insert->Module
In the new window that has appeared paste the function code.
Close the visual basic window.
add “=LModDate()” to the cell you want the date to appear in.
Make sure that cell is date formatted.
Turns out I’ve got to get Microsoft to digitally sign my macro! WTF?!
Either that or get all users to lower their security level. What a ball-ache for wanting a tiny little helpful macro in my document!
Yeah, often Microsoft causes aches. I rememeber AutoCad Revision 14, and Microsoft Visio allowed a footer on your document with: file save location, last save date, and by who. So easy. :smack:
The spreadsheet I want to add this is called OPAS.xls.
It appears like I cannot add the Module into the orginal document, it wants me to put it into my Personal.xls which I think is MY template. So only I would be able to use it, like you said. I still can’t get it to to work, I create the module, then when I add “=LModDate()” to a cell, and format it as date, it only says “#NAME?”