how to create a cell that appends the current date time when ever the row is edited
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: how to create a cell that appends the current date time when ever the row is edited
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

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

    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.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    the prices would always be changed manually

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

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

    Which columns (column letters) contain data that will be manually updated to generate a date?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mumps View Post
    Which columns (column letters) contain data that will be manually updated to generate a date?
    columns B, F, H

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

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

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,963
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by fowzan View Post
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    column X

  9. #9
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,963
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by fowzan View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •