How to Display the Last Modification Date of a MS Excel Workbook by Using MS Excel Macro
Time for a simple MS Excel macro.
Imagine you have a MS Excel document that is updated regularly or frequently. So you think it’d make sense to display the last date on which the document was modified so that your readers would know how fresh and updated the document is.
If you enter the formula
=TODAY()
it will of course enter the date on which you modify the document for the last time, that is, today’s date.
But the problem is this: when you open the document tomorrow WITHOUT making any modifications, it will display tomorrow’s date. And you don’t want that. If you make a change on September 27, 2012, you want the last modification or edit date still be “September 27, 2012″ even when the document is opened and viewed on (let’s say) January 14, 2013.
For this you need a little VBA magic.
Here is what you have to do:
(1) Fire up your VBA module (Alt + F11).
(2) Select This Workbook in the Project Manager (upper left pane).
(3) Right click and select Insert > Module.
(4) In the code window, enter the following formula:
Public Function ModifyDate()
ModifyDate = Format(FileDateTime(ThisWorkbook.FullName), “mmmm d, yyyy”)
End Function
(5) Save the formula. Switch back to your workbook (Alt + F11).
(6) Select a cell or group of merged cells where you’d like to display the date of the last modification. In the formula bar, enter:
=ModifyDate()
The date of the last modification will be displayed and stay what it is until the next time you modify your workbook:
