how to create a cell that appends the current date time when ever the row is edited

fowzan

Board Regular
Joined
Aug 5, 2015
Messages
59
i have an inventory list. the prices keep updating. as of now there is no track to when a row (be it the cost price, sell price, vendor, packing, etc) was edited. i would like to create a new column "Updated On" which stores the date whenever a row is edited.

if a row is updated twice is it possible to store 2 dates like (15-11-15||10-10-14)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,189
Usually if an action is to be performed automatically when data is changed on a worksheet such as the update you are requesting, it can be done with a Worksheet_Change macro. The only problem is that the data change (the prices in your case) must be done manually. If the price changes are done automatically or due to a formula, then the automatic adding of the date won't work.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,189
Which columns (column letters) contain data that will be manually updated to generate a date?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,189
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a change or add data in one of your columns B, F or H. Please note that the date will be added to the first unused column in that row. A date will be entered when you change data or add data in one of these columns.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B,F:F,H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim lColumn As Long
    lColumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    Cells(Target.Row, lColumn + 1) = Date
    Application.ScreenUpdating = True
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,041
Office Version
2010
Platform
Windows
i would like to create a new column "Updated On" which stores the date whenever a row is edited.
What will be the column letter designation be for this new column?
 

fowzan

Board Regular
Joined
Aug 5, 2015
Messages
59
this does the job. the only problem is that if value is edited more than once, the date is added to the next cell, and so on.
can it be added to the same cell, without deleting the old value. (latestdate||olddate||oldestdate)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,041
Office Version
2010
Platform
Windows
this does the job. the only problem is that if value is edited more than once, the date is added to the next cell, and so on.
can it be added to the same cell, without deleting the old value. (latestdate||olddate||oldestdate)
Give this event code a try...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("B:B,F:F,H:H")) Is Nothing Or Target.Count > 1 Then Exit Sub
  Application.EnableEvents = False
  If Len(Cells(Target.Row, "X").Value) Then
    Cells(Target.Row, "X").Value = Replace(Cells(Target.Row, "X").Value, ")", Format$(Date, "||dd-mm-yy)"))
  Else
    Cells(Target.Row, "X").Value = Format$(Date, "(dd-mm-yy)")
  End If
  Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,455
Messages
5,487,002
Members
407,575
Latest member
calc

This Week's Hot Topics

Top