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:

Excel Sheet For Change Event

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
VBA Code For Last Modified Date

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:

Excel Last Modified Date VBA
Excel Last Modified Date VBA

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!

4 thoughts on “The Right Way To Do Last Modified Date In Excel (Without Using TODAY)

  1. Thanks for this helpful code. I have two additional questions after trying to implement it.

    1. The code works well when manually updating the data in a given cell. However, when I increment the value in a cell using a spinner button the code does not recognize the change and does not update the date. Is there a way to adjust the code so that it recognizes incrementation using a spinner button as a change requiring a date update?

    2. Is there a way to apply this code to many cells at a time? Specifically, I have a spreadsheet with 150 spinner buttons incrementing 150 different cells. Is there a way to quickly apply this code to all these cells?

    Thanks.

    1. You are correct that a form control like the spinner will not trigger the event in the same way this post is talking about. Take a look at my post here for some tips on form controls: https://alistechtips.com/2020/07/26/excel-form-controls-in-vba/

      You’d essentially have to assign a macro to the buttons that would update a cell of your choice with the date. You could assign the macro to one spinner, make the macro dynamic by basing it off of the name or alt text of the form control, and then copy and paste that for the rest of your spinners to make it easier to implement.

      1. Thanks for the response. Are you able to provide an example of what a macro attached to a spinner would look like?

      2. Sure, When you put a spinner on the page, right click on it and select ‘Assign Macro.’ You will automatically be given a name for it. Click ‘New’ and paste in code like this:

        Sub Spinner1_Change()
        ActiveSheet.Cells(2, 5).Value = Format(Date, “mm/dd/yyyy”)
        End Sub

        In this case, the Cells(2,5) will be whatever cell address you want to write the Last Modified Date to. The right-hand side of the code will be the Date Format and date value of the cell. If you have this and then use the spinner, the specified cell will have the Last Modified Date.

        If you have a lot of spinners on your page, you can assign them one macro and do a for loop or some check to give them a unique name and cell address, but you’ll have to do a bit of figuring out for that.

Leave a Reply

%d bloggers like this: