# Auto date update cell in Excel. **URL:** \ **Category:** Factual Questions\ **Created:** [June 11, 2004, 5:36pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846 "2004-06-11T17:36:07Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 11, 2004, 5:36pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/1 "2004-06-11T17:36:07Z")
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) ---
**Author:** ![Aeschines](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aeschines/32/16251_2.png) [@Aeschines](https://boards.straightdope.com/u/Aeschines)\ **Post date:** [June 11, 2004, 5:56pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/2 "2004-06-11T17:56:07Z")
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. ---
**Author:** ![FX45\_4Ever](https://avatars.discourse-cdn.com/v4/letter/f/4bbf92/32.png) [@FX45\_4Ever](https://boards.straightdope.com/u/FX45_4Ever)\ **Post date:** [June 11, 2004, 6:42pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/3 "2004-06-11T18:42:33Z")
Lobsang, Try this: Cite [How it’s done.](http://www.mrexcel.com/archive/Dates/5993.html) 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() ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 11, 2004, 7:38pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/4 "2004-06-11T19:38:53Z")
> [@FX45 4Ever](#): > > Lobsang, > > Try this: > > Cite > [How it’s done.](http://www.mrexcel.com/archive/Dates/5993.html) > 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. ---
**Author:** ![FX45\_4Ever](https://avatars.discourse-cdn.com/v4/letter/f/4bbf92/32.png) [@FX45\_4Ever](https://boards.straightdope.com/u/FX45_4Ever)\ **Post date:** [June 11, 2004, 8:05pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/5 "2004-06-11T20:05:53Z")
😃 wow, that’s a first. I posted it but can’t seem to make it work for me. :smack: haha. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 11, 2004, 8:35pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/6 "2004-06-11T20:35:07Z")
> [@FX45 4Ever](#): > > 😃 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… 🙂 ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 11, 2004, 8:40pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/7 "2004-06-11T20:40:59Z")
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. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 11, 2004, 8:47pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/8 "2004-06-11T20:47:55Z")
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! ---
**Author:** ![FX45\_4Ever](https://avatars.discourse-cdn.com/v4/letter/f/4bbf92/32.png) [@FX45\_4Ever](https://boards.straightdope.com/u/FX45_4Ever)\ **Post date:** [June 22, 2004, 1:58pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/9 "2004-06-22T13:58:41Z")
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: ---
**Author:** ![FX45\_4Ever](https://avatars.discourse-cdn.com/v4/letter/f/4bbf92/32.png) [@FX45\_4Ever](https://boards.straightdope.com/u/FX45_4Ever)\ **Post date:** [June 22, 2004, 2:44pm UTC](https://boards.straightdope.com/t/auto-date-update-cell-in-excel/249846/10 "2004-06-22T14:44:21Z")
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?”