Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як вставити поточну мітку часу, коли дані в іншій комірці змінюються в Excel?

Припустимо, ви отримаєте аркуш, на якому потрібно змінити дані у певному стовпці. А після модифікації робочого аркуша вам потрібно вставити модифіковану позначку часу, щоб інші повідомили, які клітинки були змінені. Як з цим боротися? Ця стаття покаже вам спосіб вставлення поточної позначки часу, коли дані в іншій комірці змінюються в Excel, як показано нижче.

Вставте поточну мітку часу, коли дані в іншій комірці змінюються за допомогою коду VBA


Вставте поточну мітку часу, коли дані в іншій комірці змінюються за допомогою коду VBA

Як показано нижче на скріншоті, вам потрібно заповнити позначку часу у стовпці E, якщо відповідні клітинки у стовпці C змінено. Будь ласка, виконайте наступне.

1. На аркуші потрібно змінити та позначити позначкою часу, клацніть правою кнопкою миші вкладку аркуша, а потім клацніть Переглянути код з меню, що клацне правою кнопкою миші.

2. Потім скопіюйте та вставте наведений нижче код VBA в код вікно Microsoft Visual Basic для додатків вікно. Дивіться знімок екрана:

Код VBA: Вставте поточну мітку часу, коли змінюються дані в іншій комірці

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub

примітки: У коді цифра 3 означає, що ви збираєтеся змінити дані у стовпці C, а 5 означає, що мітка часу буде заповнена у стовпці E. Будь ласка, змініть їх відповідно до своїх потреб.

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

Відтепер при зміні даних у стовпці C позначка часу буде заповнена у відповідні клітинки у стовпці E, як показано на знімку екрана нижче.

Порада. Вибір дати, Вставити дату корисність Kutools для Excel допомагає швидко вставити дату із заданим форматом дати у вибрану комірку.
Завантажте та спробуйте зараз! (30-денний безкоштовний маршрут)


Статті по темі:


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Gerald.First12 · 1 months ago
    Hi, finally I found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
  • To post as a guest, your comment is unpublished.
    Gerald · 1 months ago
    Hi, finally i found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
  • To post as a guest, your comment is unpublished.
    Paul · 7 months ago
    Hi, This is great info, but I'm having problem. I would like to use for change in column A time stamp column B and change in column D with time stamp column E and change in column G with time stamp column H. I can not figure out. Help please.
  • To post as a guest, your comment is unpublished.
    Lina · 11 months ago
    Hi, Is there a chance you can help me? I have tried the VBA code for the timestamp:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20180830
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    Dim xDPRg, xRg As Range
    xCellColumn = 3
    xTimeColumn = 5
    xRow = Target.Row
    xCol = Target.Column
    If Target.Text <> "" Then
    If xCol = xCellColumn Then
    Cells(xRow, xTimeColumn) = Now()
    Else
    On Error Resume Next
    Set xDPRg = Target.Dependents
    For Each xRg In xDPRg
    If xRg.Column = xCellColumn Then
    Cells(xRg.Row, xTimeColumn) = Now()
    End If
    Next
    End If
    End If
    End Sub

    My problem is that I have to add dates in column 22 and I need the timestamp in column 21. The above code does not work in my sheet. I have tried it with timestamp functions and formulas, but it always changes it to the date when I opened the workbook.

    I also tried to work with conditional formatting that the another cell changes the colour when the cell with the timestamp is not todays date, but this does not work either. Can you let me know what I do wrong?
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Hi Lina,
      Do you mean manually enter dates in column 22 and then populate timestamp automatically in adjacent cells in column 21?
      You just need to change the column numbers in the code to get it down. If you only want to display time, replace the NOW() with Time() in the code. See the below code.

      Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 20201106 Dim xCellColumn As Integer Dim xTimeColumn As Integer Dim xRow, xCol As Integer Dim xDPRg, xRg As Range xCellColumn = 22 xTimeColumn = 21 xRow = Target.Row xCol = Target.Column If Target.Text <> "" Then If xCol = xCellColumn Then Cells(xRow, xTimeColumn) = Time() Else On Error Resume Next Set xDPRg = Target.Dependents For Each xRg In xDPRg If xRg.Column = xCellColumn Then Cells(xRg.Row, xTimeColumn) = Time() End If Next End If End If End Sub
      • To post as a guest, your comment is unpublished.
        Lina · 11 months ago
        Thank you for your response. Yes, that's what I wanted to do. I tried the above VBA code before but for some reason it did not work, but it works fine now. Thank you for your help
  • To post as a guest, your comment is unpublished.
    Max · 1 years ago
    I have copied this code into VBA, but no dates are being added to the column. How do I run this macro/sub?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Max,
      You need to paste the code into the corresponding sheet's code window (as the below screenshot shown), after pasting the code, change the data and the date column numbers based on your needs. When return to the worksheet and change any cell value in the data column, the code will take effect automatically and populate the time stamp into the specified date column cell.
  • To post as a guest, your comment is unpublished.
    NannyKate · 1 years ago
    Looking to enter data in columns D, E & F with a time stamp in the respective row in column G
  • To post as a guest, your comment is unpublished.
    Baltimorejack68 · 1 years ago
    Is there a way to clear the time stamp if the content of column C is deleted?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      The below code can help. Please have a try. Thank you.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'Updated by Extendoffice 20200529
      Dim xCellColumn As Integer
      Dim xTimeColumn As Integer
      Dim xRow, xCol As Integer
      Dim xDPRg, xRg As Range
      xCellColumn = 3
      xTimeColumn = 5
      xRow = Target.Row
      xCol = Target.Column

      If xCol = xCellColumn Then
      If Target.Text <> "" Then
      Cells(xRow, xTimeColumn) = Now()
      Else
      Cells(xRow, xTimeColumn) = ""
      End If
      Else
      On Error Resume Next
      Set xDPRg = Target.Dependents
      For Each xRg In xDPRg
      If xRg.Column = xCellColumn Then
      If Target.Text <> "" Then
      Cells(xRow, xTimeColumn) = Now()
      Else
      Cells(xRow, xTimeColumn) = ""
      End If
      End If
      Next
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Erich · 2 years ago
    Hi Crystal,

    Thank you so much for your help thus far, your code works perfectly, I changed one line so it changes when a specific value is added in a cell now I have another idea, it would be amazing if you could help.

    I want to track a manufacturing process. I have a drop down list where progress is selected as the product is manufactured. The code that I am thinking about goes as follows:

    When the product is completed Column L gets changed to "Completed" and Column M gets a time stamp, when the product is delivered Column L changes again to "Delivered" and then Column N gets a timestamp.

    Is this possible? Please let me know if I can send a screenshot if my message is unclear.

    Thank you so much, hope you can help.
    • To post as a guest, your comment is unpublished.
      Kevin · 1 years ago
      Enable iterative calculations under options > formula > "check" enable iterative calculations set the number to like 20


      This should work... it will essentially make excel do a loop that it will stop calculating after 20 times through and leave a timestamp in the respective column.

      The first one in column M will look like:
      "=IF(L1="Completed",IF(M1<>"",M1,NOW()),"")

      The second formula will look the same except change "completed" to "delivered" and "M1" to "N1"

  • To post as a guest, your comment is unpublished.
    Hannah · 2 years ago
    Many thanks for this - set it up yesterday and all worked perfectly. However I have updated cells today and the dates do no seem to update. I have also tried to duplicate the code on a second tab today and this is not working.

    Any ideas?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Have you saved the workbook as an Excel Macro-Enabled Workbook?
      The code works well in my case.
  • To post as a guest, your comment is unpublished.
    shri · 2 years ago
    Hi Crystal,
    I am trying to create a function where If I update any Cell in H9:L9 in Sheet 1, timestamp in cell C2 in Sheet 2 should get updated.
    Now I need to do this on about 100 rows in Sheet 1( H10:L10, H11:L11) and update corresponding C3, C4....in Sheet 2

    Any help is greatly appreciated. Cheers.!
  • To post as a guest, your comment is unpublished.
    Luis.nsv · 2 years ago
    5/15/19|1:41 PM|John Smith|125B-1|10000000|Equipment|Staff|5/15/19|2:43 PM|Staff

    The above post shows the data that is input into a row. I was seeking help updating the first 2 columns that contain a timestamp whenever the ID (10000000) column is populated. If possible could the timestamps also be removed if the ID is also removed? Concerning the last 3 columns (date|time|staff) could those timestamps get updated whenever the last column is populated with a staff name. I tried playing around with the code but my lack of knowledge with VBA only allowed me to do so much.
  • To post as a guest, your comment is unpublished.
    JediTrader · 2 years ago
    Gents,

    It has been a month I am looking for a similar solution like this one. While the above solution seem to be OTM, I am using this for the stock market.
    So I have Column F where I have implemented a Buy/Sell strategy and the sheet continues to refresh every 1 minute. I have Column T where I want the time stamp based on the following conditions :

    (1) Column F - Signals a Buy or Sell
    (2) Column F - Changes from a Buy to Sell or Sell to Buy
    (3) Column F - Changes from a Buy/Sell to empty

    While the general solution that is available on the net seem to be working (excel formula), but when the data refreshes it punches the current time than the signal time. For e.g. If I get a Buy @ 9:15 hrs and if the current time is 10:30, I get to see 10:30 hours in the Column T (Signal time) and not 9:15 hours.

    Any assistance would be greatly appreciated.

    Warm Regards
    JT
  • To post as a guest, your comment is unpublished.
    spen · 2 years ago
    Would the timestamp functionality be bypassed if the excel file you're using is a refreshable?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      The code does not support connections data. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    sh.senthilkumar@gmail.com · 2 years ago
    Hello Everyone

    Thanks in advance for your help.

    What should be code to update the current time on the cell in column X if there is a change in any cell on that row? For example any change in values on cells a2 to w2, then the time should be to updated in x2.


    Thanks Again.
  • To post as a guest, your comment is unpublished.
    Farid · 2 years ago
    Hi Crystal, this is really good, how can I get the timestamp change in each corresponding row when any cell from the range of cells in the respective row changes. For example, A2 should get updated with timestamp when any changes are done on second row between B2 to H2 and similarly A3 gets the timestamp when changes done on B3 to H3, and so on until the last row.
  • To post as a guest, your comment is unpublished.
    Sander · 2 years ago
    Adding the timestamp works very good and is very useful. However I receive an error when I delete a row, because the script cannot insert the timestamp in the row that does not exist anymore.


    Would be great if someone can advice how to solve this.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Sander,
      There is no error occur in my case. Can you tell me your Excel version? Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Jessica · 3 years ago
    I have a project I'm working on that I update regularly. It's mostly just for keeping track of my employee's information like phone numbers, drivers license expiration, etc. Since I update it so frequently, I want an automatic timestamp that rests at the top or bottom of my worksheet that will update with today's date everytime I update information ANYWHERE on the spreadsheet. I currently use A-N and 1-42 so I need a stamp around M40. How would that code look?
    • To post as a guest, your comment is unpublished.
      Tychabrahe · 3 years ago
      Open Developer and on your workbook select the Workbook and the BeforeSave event.


      In the sub put the code: Range("M40").value = Now
  • To post as a guest, your comment is unpublished.
    Alec · 3 years ago
    Hi,


    Is it possible to alter the code so that any changes in multiple columns would generate a new timestamp in the same cell? I.e. I make an edit in anyone of cells A3, B3 or C3 and the timestamp updates just in C4?


    Also, is it possible to make the code apply only to a specific range within a sheet?


    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Alec
      Please try the below VBA code. Thank you for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRRg, xRg As Range
      Dim xRgArray As Variant
      Dim xStrCell As String
      Dim xSCRg As Range
      Dim xStrResult As String
      xRgArray = Array("A1:C10")
      xStrCell = "A3, B3, C3"
      xStrResult = "C4"
      On Error Resume Next

      If Target.Cells.count > 1 Then Exit Sub
      For xFNum = LBound(xRgArray) To UBound(xRgArray)
      If xRgArray(xFNum) <> "" Then
      Set xRg = Range(xRgArray(xFNum))
      If Not xRg Is Nothing Then
      Set xSCRg = Null
      Set xSCRg = xRg.Range(xStrCell)
      xSCRg.Select
      If Not xSCRg Is Nothing Then
      If Not Intersect(xSCRg, Target) Is Nothing Then
      Set xRRg = xRg.Range(xStrResult)
      If xRRg Is Nothing Then Exit Sub
      xRRg.Value = Now()
      Exit For
      End If
      End If
      End If
      End If
      Next xFNum
      End Sub
      • To post as a guest, your comment is unpublished.
        Rachel · 1 years ago
        Hey, this is sort of similar to what I require, i'd like for a timestamp to be updated in column AG when cells between A and AH are updated.

        I've tried the above code but have had no luck.
      • To post as a guest, your comment is unpublished.
        Blaze · 2 years ago
        Hey, hopefully you still see this!!! I'm using the code below for a timestamp update in C6 anytime C5 is updated. The problem is that when I'm in the rest of the worksheet, anytime I hit enter or tab, it jumps me back to C5. Any thoughts?

        Dim xRRg, xRg As Range
        Dim xRgArray As Variant
        Dim xStrCell As String
        Dim xSCRg As Range
        Dim xStrResult As String
        xRgArray = Array("C5:C6")
        xStrCell = "C5"
        xStrResult = "C6"
        On Error Resume Next

        If Target.Cells.Count > 1 Then Exit Sub
        For xFNum = LBound(xRgArray) To UBound(xRgArray)
        If xRgArray(xFNum) <> "" Then
        Set xRg = Range(xRgArray(xFNum))
        If Not xRg Is Nothing Then
        Set xSCRg = Null
        Set xSCRg = xRg.Range(xStrCell)
        xSCRg.Select
        If Not xSCRg Is Nothing Then
        If Not Intersect(xSCRg, Target) Is Nothing Then
        Set xRRg = xRg.Range(xStrResult)
        If xRRg Is Nothing Then Exit Sub
        xRRg.Value = Now()
        Exit For
        End If
        End If
        End If
        End If
        Next xFNum
        End Sub
  • To post as a guest, your comment is unpublished.
    basselo · 3 years ago
    Hi,

    This is exactly what I was looking for! However I was wondering if you can help me get it to work with a protected worksheet. It only seems to work on an unprotected one (when the timestamp updates due to a change in value as part of a formula, similar to Dennis' request).

    Thank you so much! Bassel
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi,
      This can also work on a protected worksheet. You need to format the changed cells and the timestamp cells to unlocked status in advance before protecting the worksheet. Please have a try again. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Dennis · 3 years ago
    thanks so much for this, however the timestamp only changes if the value in the cell is changed "manually". If it's changed due to a change in another cell and the value changes as part of a formula, there is no timestamp. Do you have a solution for this? thanks! Dennis
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Dennis,
      The code has been updated with the problem solved. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Diego · 3 years ago
    Hi, This works perfectly if I manually change the cell, when I copy and paste information is not working. I'm trying to have this timestamp every time someone pastes new information. Rather than changing cell information one by one, the behavior I want to capture is when the data is pasted in bulk for multiple cells. Does somebody know what I should do?


    Thanks,
  • To post as a guest, your comment is unpublished.
    Leslie · 3 years ago
    Thank you so much for this! I use scroll buttons to add 1 to my total every time I complete a task, but macros don't register changes to the buttons. After doing some more research I figured out how to assign a macro to my control buttons and now I can track my changes!!!! I was making it more complicated than it need to be, this is what I ended up with:


    Sub Timestamp()
    '
    ' Timestamp Macro
    ' Timestamp on scroll buttons
    '
    '
    Range("j2").Value = Now()

    End Sub
  • To post as a guest, your comment is unpublished.
    Amelius · 3 years ago
    I've got a database in excel that I need to keep track of what has been changed.
    Is it possible to modify the code so that if changes are made in sheet 1, it will duplicate the changed cell in sheet 2 and provide a time stamp beside it?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Sorry can't help with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    suresh.thangaiyan83@gmail.com · 3 years ago
    Hi, can be done if any values are changed and the timestamp is updated only in a particular cell like A1
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      If you want to always display timestamp in cell A1 if any values are changed in a certain range. The following VBA code can help.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Target, Range("B1:D4"))
      If xRg Is Nothing Then Exit Sub
      Range("A1").Value = Now()
      End Sub
      • To post as a guest, your comment is unpublished.
        Farid · 2 years ago
        hi Crystal, this is really good, how can I get the timestamp change in each corresponding row when any cell from the range of cells in the respective row changes. Like A2 should get updated with timestamp when any changes are done between B2 to H2 and so on with A3, A4 etc.
  • To post as a guest, your comment is unpublished.
    Mary · 3 years ago
    What is the code if I want to timestamp several cells individually in the same worksheet?
    Example: Change in column 3, timestamp in column 5. Change in column 10, timestamp in column 11. Change in column 13, timestamp in column 14.
    xCellColumn = 3
    xTimeColumn = 5
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Mary,
      The following VBA code can help you. Thank you for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim I As Long
      Dim J As Long
      Dim xCols As Long
      Dim xSRg As Range
      Dim xRgEx As Range
      Dim xSRgArea As Range
      Dim xNextArea As Range
      On Error Resume Next
      Application.EnableEvents = False
      Set xSRg = Union(Range("C:C"), Range("E:E"), Range("J:K"), Range("M:N"))
      Set xRgEx = Intersect(xSRg, Target)
      If xRgEx Is Nothing Then GoTo ExitSub
      For I = 1 To xSRg.Areas.Count
      J = I
      Set xSRgArea = xSRg.Areas.Item(I)
      If xRgEx.Column = xSRgArea(1).Column Then
      xCols = xSRgArea.Columns.Count
      If xCols = 1 Then
      Set xNextArea = xSRg.Areas.Item(J + 1)
      If xNextArea Is Nothing Then
      GoTo ExitSub
      ElseIf xNextArea.Columns.Count = 1 Then
      xNextArea(xRgEx.Row).Value = Now()
      Exit For
      End If
      ElseIf xCols = 2 Then
      xSRgArea.Columns(2).Rows(xRgEx.Row).Value = Now()
      Exit For
      End If
      End If
      Next
      ExitSub:
      Application.EnableEvents = True
      End Sub
      • To post as a guest, your comment is unpublished.
        britni.norris@gmail.com · 2 years ago
        Can you share what needs to be updated to make it specific to another spreadsheet? For instance, changes in C timestamp in E, changes in G timestamp in I, changes in O timestamp in Q.
        • To post as a guest, your comment is unpublished.
          W D · 2 years ago
          I simply strung the first code mentioned above together, changing all the variables from VAR to VAR1/VAR2/VAR3 etc
  • To post as a guest, your comment is unpublished.
    hotkya · 3 years ago
    I am creating a work sheet in Excel where I want to track Start and End time of a particular transaction using a timestamp. A column will also show the time duration. Please see picture below. Can someone help me? Image below.

    I found 2 VBA Codes that records time stamps. However, I don't know how to merge them so they will work in this sheet:


    Code 1 . It shows timestamp in "Start Time" column (B4) when you enter a transaction number in "Case ID" coumn (A4).


    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Target.Column = 1 And Target.Value <> "" Then
    Application.EnableEvents = False
    Target.Offset(0, 1) = Format(Now(), "mm/dd/yyyy HH:mm:ss")
    Application.EnableEvents = True
    End If
    Handler:
    End Sub


    Code 2. It shows time stamp in "End Time" column (D4) if a selection from a dropdown in "Status" column (C4) is selected.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    xCellColumn = 3
    xTimeColumn = 4
    xRow = Target.Row
    xCol = Target.Column
    If Target.Text <> "" Then
    If xCol = xCellColumn Then
    Cells(xRow, xTimeColumn) = Now()
    End If
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear chia,
      Did not see your image. Please click the below Upload files button to upload your picture.
  • To post as a guest, your comment is unpublished.
    november · 4 years ago
    how can we lock the time format.. cuz it updates everytime and inserts date and time.
  • To post as a guest, your comment is unpublished.
    eric · 4 years ago
    I am pasting multiple rows and columns worth of data (at one time, and always at the same time). i'd only like for the time stamp to display in one cell. in other words, whenever cell C9 changes (the top left corner of my data), display the time in cell A1.

    any help would be much appreciated!
  • To post as a guest, your comment is unpublished.
    Abdul · 4 years ago
    Hi,

    The above formula will be useful for single cell time stamp update

    Ex: If we enter any data in 3rd cell, time stamp automatically updated in 5th cell.

    Please advice for multiple cell update.

    Ex: If we enter any data in 3rd cell, time stamp automatically updated in 5th cell and if we enter on 4th cell, it will update on 6th cell.
    • To post as a guest, your comment is unpublished.
      Tyler · 3 years ago
      I too am interested in this exact question. Have you found an answer? Will my commenting on this draw anyone else's attention to the question?!