Show last date a cell was modified

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi Guys!

I have a table in which the A column indicate the status of a task and the other columns are information regarding that task. I want to have a column (K) that indicates the last time the A cell of the same row as been modified. All data is inputted using a userform, which means the formula in the K cells must be inputted automatically and refer to the A cell of the same row.

Everything works for the data input and the K cell value do change when the A cell value changes(I was using a timestamps with minutes when I tested), however for some reason the dates seems to be updated daily ; when I opened the workbook this morning, all dates were today's -_-

Here is what I've done so far ;

1) Create a function that generate the date if the reference change

VBA Code:
Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yyyy")
Else
Timestamp = ""
End If
End Function

2) Defined the value of the K Cell in the userform VBA ("État" is the name of the A column header)

VBA Code:
.Cells(iRow, 11).Value = "=Timestamp([@État])"

Thank you for your time!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
I believe it is because you are assigning the function like a formula and it gets recalculated each time you open the workbook. Maybe just use .Value = Timestamp() ?
 

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I believe it is because you are assigning the function like a formula and it gets recalculated each time you open the workbook. Maybe just use .Value = Timestamp() ?
I need to put a reference in my Timestamp function so it knows when to update ; The formula wouldn't work that way, and if it did it wouldn't update when the value of the A cell of the same row was changed. Maybe using that function isn't the best way to do it but it is still the only way I see.
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
You can use the worksheet_change event to catch the change time.

VBA Code:
If Not (Intersect(Target, Reference) is Nothing) then
    ' Code Here
End If
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,849
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@AlexSrois are you sure you want to handle it like that? If Column A value is changed from something to "" you want the Column K to = "" ?
 

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
@AlexSrois are you sure you want to handle it like that? If Column A value is changed from something to "" you want the Column K to = "" ?
I might be explaining something wrong, and probably misunderstanding a ton too :)

Column A is a dropdown list of status. If that status is changed, I want the date of the change to be displayed in the K column, on the same row. That way, for each row, we can see when the status is changed.

Here is my understanding of what I'm doing so far ;

VBA Code:
If Reference.Value <> "" Then
' If Reference.Value is different from ""
Timestamp = Format(Now, "dd-mm-yyyy")
' Display today's date

VBA Code:
.Cells(iRow, 11).Value = "=Timestamp([@État])"
'Set the 11th row value to =  Timestamp formula with ''Etat" (A Column) as the reference value

Like I said, this works perfectly, except from the fact that when the workbook is opened, the timestamp seems to trigger.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,849
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok make a Test copy of that workbook that you can play with.

Then instead of using the any of the code from your Post #1 here, try the following:

1) Right click on the sheet tab at the bottom of the sheet that you want to monitor the change in the Column A, select view code.
2) copy the following code into the window that pops up:

VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
'
    Dim RowThatChanged  As Long
'
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        RowThatChanged = Target.Row                                                                     ' Save the row value that was changed
        Range("K" & RowThatChanged).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub

3) Save that test workbook, close it then reopen it and see what happens when the Column a values are changed. The K column should reflect the date.
4) save the test workbook again and reopen it on tomorrow's date and see if the Column K values still retain the date from today.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,849
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Here is a shorter version of the code that should work instead of the the code in my previous post:

VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        Range("K" & Target.Row).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub
 
Solution

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
You can use the worksheet_change event to catch the change time.

VBA Code:
If Not (Intersect(Target, Reference) is Nothing) then
    ' Code Here
End If
With johnnyL explanation I understand better what you meant and you seemed on the right path! Thank you for your time!
 

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Here is a shorter version of the code that should work instead of the the code in my previous post:

VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        Range("K" & Target.Row).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub
That works perfectly!
I didn't about this ''Intersect'' thing ; will spend some time learning this. Thank you so much!
 

Forum statistics

Threads
1,148,250
Messages
5,745,638
Members
423,965
Latest member
visionquest1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top