Insert the date an Excel Workbook was last modified.

Anonymous
2010-11-17T00:13:29+00:00

Is there a way to insert, into a cell, the date that a workbook was last modified?

Perhaps by getting the modified date from the file's properties into a cell.

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-11-17T00:29:39+00:00

Hi,

To modify a workbook you have to save it so we can use the before_save event

ALY+F11 to open vb editor. Double click 'ThisWorkbook' and paste this code in on the right. Change the sheet and range to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("A1").Value = Now

End Sub

If this post answers your question, please mark it as the Answer.

Mike H

Was this answer helpful?

100+ people found this answer helpful.
0 comments

86 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-08-07T09:05:00+00:00

    Thanks, Dinesh, for the detailed instructions, but it did not work for me (using Excel 2010). I copied your code into the new "Module 1" under "Modules", saved the Excel file from within the VB window (I assume that is what you mean by "Save this file"?), then did "File, Close and Return to Microsoft Excel". When I start typing "= LastSaveDate" into a cell, the text populates automatically with the full LastSaveDate name (so it is present), but after I've hit Return the #NAME? error appears in the cell.

    Does anyone have any ideas where I'm going wrong?

    Just to agree with others, this is a very useful function that should be a standard function in Excel.

    Jon K

    Jon K - I too had the same issue - And realized it was because I had not enabled macros in the file. If you enable the macro - It works OK

    WK

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-07-02T14:08:30+00:00

    Worked perfect. Thanks.

    I also found the macros on the following website to be useful putting the Last Saved Date in the Footer or Header.

    http://excelribbon.tips.net/T007764_Displaying_the_Last_Modified_Date.html

    I'm not a MVP in Excel, but I was able to get this to work. I agree MS should simply have a button to insert this function into Headers and Footers, as this is a basic need for my Project Management Team.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-06-26T19:50:50+00:00

    I am trying to use the vba code below as well:

    Function LastSaveDate()

        Application.Volatile True

        LastSaveDate = FileDateTime(ThisWorkbook.FullName)

    End Function

    You can call this from a cell with

    =LastSaveDate()

    and I keep getting a #NAME? error whenever I put =LastSaveDate() into the cell that I want the timestamp to be.

    Anybody know why this is occurring?

    Well, over a year later, but I think I have an answer...

    I was copy/pasting the =LastSaveDate() into the cell and got the same #NAME? result. When I hand-typed it in, it worked. I don't know if that's a universal solution or I just tried it enough and Excel finally conceded defeat. Worth a shot though...

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-06-16T18:58:28+00:00

    Well written. Thanks!

    Was this answer helpful?

    0 comments