Excel: How can I add "Last Modified" to Excel

Question

Excel: How can I add "Last Modified date" in a cell on an Excel spreadsheet every time I save it? Office 2019

Answer 1

I clicked on Data validation but did not see "Use a formula to determine which cells to format" option.

Under settings I had “Any Value, Whole number, Date, Time”, etc., 

Under “Input Message”
And “Error Alert”
 
So I inserted the code: =TODAY() & " " & NOW(), In “Insert Function” This just returned a long number

I went on to the second option
Created a module and pasted into the dialog box:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strDate As String
    strDate = Now()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = strDate
End Sub
Saved the module
Went back to Excel, saved the workbook.

Nothing

Neither option worked. I’m using Office Pro 2019 will that make the difference?

Answer 2

You can add the "Last Modified" date and time to an Excel spreadsheet in several ways:

1. You can add a formula in a cell that automatically updates the "Last Modified" date and time whenever the spreadsheet is saved. The formula you can use is: =TODAY() & " " & NOW().
2. You can create a macro that inserts the current date and time in a specific cell every time the spreadsheet is saved. To do this, follow these steps: a. Enable the Developer tab (if it's not already visible) in Excel's ribbon by following the steps mentioned in my previous response. b. Click on the Developer tab, then click on the Visual Basic option in the Code group. c. In the Visual Basic Editor, select Insert > Module. d. In the Module, paste the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strDate As String
    strDate = Now()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = strDate
End Sub

Note that in the code above, "Sheet1" is the name of the sheet where you want the date to appear, and "A1" is the cell address. You can modify these values as necessary to reflect the sheet and cell you want to use.

1. Excel 365 provides a "Timestamp" feature that allows you to automatically add the current date and time to a cell whenever the value in another cell is changed. To use this feature, follow these steps: a. Select the cell where you want the timestamp to appear. b. Click the Data tab in the ribbon. c. Click the Data Validation button in the Data Tools group. d. In the Data Validation dialog box, select the "Use a formula to determine which cells to format" option. e. Enter the formula =TODAY() in the "Format values where this formula is true" field. f. Click OK to close the dialog box.

These are some ways you can add the "Last Modified" date and time to an Excel spreadsheet. You can choose the method that works best for your needs.

Answer 3

Thank you for this! It has been a year since I tried and gave up trying to do this.

I entered the Code:

Private Sub Workbook_Open(G:\Alaska\Alaska Costs.xlsm)

Range("C2").Value = FileDateTime("G:\Alaska")

End Sub

However I'm still missing something as it did not work.

Answer 4

hi Terry. not sure if you have fixed the issue.

correct codes are:

Private Sub Workbook_Open()

Range("C1").Value = FileDateTime("file path")

End Sub

file path = C:.....desktop

do not include worksheet name in file path

Answer 5

Or Like this