The Right Way To Do Last Modified Date In Excel (Without Using TODAY)

Excel still holds an important place in a lot of the business software world. Underneath its simple spreadsheet is a powerful tool with its own robust coding language (Visual Basic) and frameworks. I recently ran into an interesting problem when I was working on an Excel project:

How do I store the last modified date of a cell in another cell?

This seems simple enough in concept, but the solutions I was coming across after doing some research were variations of using the TODAY function or having NULL checks and formulas. Neither of these options were viable because TODAY updates everytime the sheet is recalculated, losing my last saved date. Formula fields got too messy and would often need a default date initially, which looked cluttered.

After doing some more reading, I came across the answer:

VBA Events!

Excel captures certain events and allows certain events to have parameters that can be used for extra processing. The event we will use to implement a Last Modified Date in Excel is the Change Event.

Change fires whenever a cell is changed by a user or an external link and when it fires it passes the Range of what triggered it as a parameter. Plainly, this means that you know what cell triggered the change. You can use the cell’s properties to build complex logic that will then only run code when a certain cell is fired.

Let’s start illustrating this by opening Excel and creating a simple spreadsheet. It will have one column with a field for user input and another column that will hold the Last Modified Date of the cell adjacent to it:

To use the Change event, right-click on the bottom of the tab where it says ‘Sheet1’ ad click ‘View Code’:

This opens up the VBA panel, which is where you write VBA code to have Excel compute more sophisticated operations. Paste the following snippet in the panel for ‘Sheet1’:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 2 Then
        ActiveSheet.Cells(2, 2).Value = Format(Date, "mm/dd/yyyy")
    End If
End Sub

Excel routines are specified by the keyword Sub. Worksheet_Change(ByVal Target As Range) is how the Change event is called. This code will run every time a Change event happens in our worksheet. Target is name of the variable being passed in, and it is of type Range. Range is a data object VBA uses that represents a range of cells.

VBA supports If statements like any coding language. The syntax is as follows:

If [TRUE / FALSE condition] Then
    (your code here)
ElseIf [TRUE / FALSE condition] Then
    (your code here)
Else
    (your code here)
End If

In our case, we only have one condition to check, so our if statement is really simple. Range objects have the properties ‘Column’ and ‘Row,’ which return the long values of their respective columns and rows. For this example, we only want to record the Last Modified Date cell A2. It’s row and column are 2 and 1, respectively, so we place that condition in our If statement.

If this condition is met, we want to set the value of the cell next to it, B2. To set a cell’s value, use the syntax:

ActiveSheet.Cells([Row Value], [Column Value]).Value = [Value]

We are going to set our cell to today’s date, so we use the Date object and the Format method, passing in a month, day, and year format to display our date in.

That’s it!

Now let’s see our code in action:

Perfect! Since this method does not really on TODAY, the value will not change. Also, there is no formula needed so the cell will not have a default value or anything messy in it if the cell it is tracking has not been modified.

To learn more about Excel VBA concepts, check out some of my other posts about looping and conditionals. Find this post helpful? Reach out to me using the Contact button at the top of this post if you want a consultation for your tech-related issues!