How to Automatically Update Edit Time in Excel File
How to Automatically Update Edit Time in Excel File: When working on a shared Excel file, its contents are likely to be edited multiple times over different periods. To better manage the content of the Excel file or to have a clear understanding of when others have edited the file, you should insert an automatic update time into Excel. This way, information about the creation time and the latest edit time of the file will be recorded and displayed in Excel, allowing you to know when the content was last changed. Follow the steps below to learn how to automatically update the edit time in Excel.
Step 1
First, open the Excel file you want to automatically update the edit time on. Then select the sheet where you want to update the file edit time. Next, use the shortcut Alt + F11 to open the Microsoft Visual Basic for Applications window.
Step 2
After the VBA window appears, select the tab on the toolbar. The scroll bar appears, then select the Module option.
How to Automatically Update Edit Time in Excel File
Step 3
Sub Workbook_Open()
Range("A20").Value = Format(ThisWorkbook.BuiltinDocumentProperties("Creation Date"), "short date")
Range("B20").Value = Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "short date")
End Sub
Where:
- A20 is the location of the file creation time
- B20 is the location of the latest edit time of the file.
Step 4
After entering the code, click on the Run Sub/UserForm icon on the toolbar. Alternatively, you can use the F5 shortcut key to run the code.
How to Automatically Update Edit Time in Excel File
Step 5
After running the VBA code, go back to the Excel worksheet, and the file creation time and latest edit time will appear in cells A20 and B20. Now you can format and annotate these times for better understanding.
That’s it. Every time someone edits this Excel file, the time in cell B20 will be automatically updated quickly. You can use this to manage who edited the data and when it was edited.
How to Automatically Update Edit Time in Excel File
Thus, this article has shown you how to automatically update the edit time in Excel files. Hopefully, it will be useful to you in your work. Good luck!
