Find last modified time stamp for a cell.

[Tejas Kore]

Hi Friends,

I wanted to know how can we get last modified time for a cell besides that cell using excel formula.

For eg: If you have some data in cell A1 ... You make changes to this cell A1 and I want to get the time at which this change was made in cell B1 using excel formulas.

 

[Joe4]

You would have to use VBA to capture the date/time a cell was updated and store that in a cell somewhere.
A common methodology known as "date stamping" is using VBA to record when a specific cell us updated and write that to a particular cell.

Here is what that code would look like. Whenever cell A1 is manually updated by itself, this will update the date/time in cell B1.
To put this code in the proper place, right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one cell is updated
    If Target.Count > 1 Then Exit Sub
    
'   Run if cell A1 is updated
    If Target.Address = "$A$1" Then
'       add date/time stamp to cell B1
        Target.Offset(0, 1) = Now()
    End If
    
End Sub

 

[Tejas Kore]

Hi Joe,

Thanks for your reply... I got it and it is working perfectly fine.
I had one more question what changes needs to be done in order to apply for a range instead of a cell ?

 

[Joe4]

I had one more question what changes needs to be done in order to apply for a range instead of a cell ?

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim isect As Range
    Dim cell As Range

'   Set range to apply to
    Set rng = Range("A:A")
    
'   See if updated cell(s) fall in range
    Set isect = Intersect(rng, Target)
    
'   Exit if updated cell not in our range
    If isect Is Nothing Then Exit Sub
    
'   Update time/date stamps
    For Each cell In isect
        cell.Offset(0, 1) = Now()
    Next cell
    
End Sub