提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在單元更改時自動記錄日期和時間?

我們很容易手動插入靜態日期和時間,或插入一個動態的日期與一個公式的系統時間。 如果您想要在更改或輸入值時自動記錄日期和時間,則此問題可能與處理有所不同。 但是,在本文中,您可以通過以下步驟解決此任務。

使用VBA代碼更改單元格時自動記錄日期和時間

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文字; 將數字和貨幣轉換為英文單詞。
  • 合併工具:將多個工作簿和工作表合二為一; 合併多個單元格/行/列,而不會丟失數據; 合併重複的行和總和。
  • 拆分工具:根據價值將數據分割成多個工作表; 一本工作簿可轉換為多個Excel,PDF或CSV文件; 一列到多列。
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 將個性化電子郵件批量發送給多個收件人。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右泡 使用VBA代碼更改單元格時自動記錄日期和時間


例如,我有一個值的範圍,現在,當我在列B中更改或鍵入新值時,我想要自動記錄C列中的當前日期和時間,如下面的截圖所示:

DOC更新時間 - 值的變化,1

您可以使用以下VBA代碼完成此任務。 請這樣做:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications窗口。

2。 然後從左邊選擇你使用過的工作表 項目瀏覽器,雙擊它打開 模塊,然後將以下VBA代碼複製並粘貼到空白模塊中:

VBA代碼:當單元格更改時自動記錄日期和時間

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

DOC更新時間 - 值的變化,1

3. 然後保存並關閉此代碼以返回工作表,現在當您更改單元格值或在列B中鍵入新數據時,日期和時間將自動記錄在C列中。

筆記:

1。 在上面的代碼中,你可以修改“B:B“到任何其他想要更改此腳本中的單元格值的列: Set WorkRng = Intersect(Application.ActiveSheet.Range(“B:B”),Target).

2。 有了這個 xOffsetColumn = 1 腳本,您可以插入日期和時間並更新到更改值列旁邊的第一列,您可以將數字1更改為其他數字,例如2,3,4,5 ...這意味著日期將插入第二,第三,第四或除了您更改的值列之外的第五列。

3。 當您刪除已更改列中的值時,日期和時間也將被刪除。


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Shawn · 5 days ago
    It is actually not working with my drop down list, is there any solution to that?
  • To post as a guest, your comment is unpublished.
    Sergio · 1 months ago
    Great tutorial, exactly what I needed. Thanks for posting.
  • To post as a guest, your comment is unpublished.
    Ian Cunningham · 3 months ago
    The code in "VBA code: Record date and time automatically when cell changes" above works well in my worksheet where I manually enter data in a cell. However, I have a "summary" worksheet in which cells automatically update their value based upon the values of cells on other worksheets in the same workbook, e.g. The formula in my cell B6 on my 'summary' worksheet might be ='Example'!F3. When the value in 'Example!F3 changes the summary sheet cell B6 automatically updates to a new value. I am trying to show a timestamp on the summary sheet when ever changes occur to save me having to search through numerous linked worksheet for changes. With the present VBA code nothing happens when the value on my 'summary' sheet updates from the data on the linked worksheet cells. Can you help with this please?
  • To post as a guest, your comment is unpublished.
    Hannes · 10 months ago
    Would it be possible to have the timestamp appear 5 rows down? Would need it in an specific cell and not the entire column.
    Column range is easy to change to specific cell but i don´t know how to get the timestamp in an specific cell :(
    Please help!
  • To post as a guest, your comment is unpublished.
    Hannes · 10 months ago
    Would it be possible to change on what row the timestamp is showed? I would like to have the timestamp 5 rows down.
    Tried xOffsetRows = 5 but that did not word, maybe that command does not exist :)
    Please help!
  • To post as a guest, your comment is unpublished.
    Cody · 10 months ago
    what about if i need to have column I, J, and K all trigger a date into column L? I have tried posting script 3 times, and changing the name, and column offset, but it never runs the second and third script.
  • To post as a guest, your comment is unpublished.
    Cody Baird · 10 months ago
    also, i had to change the first line of code to be different for each copy/paste.
    example
    Private Sub Worksheet_Change (ByVal Target As Range)

    the next is

    Private Sub Worksheet_Change1 (ByVal Target As Range)


    by doing so i eliminate an error Compile Error: Ambiguous name detected: Worksheet_Change.

    if i duplicate this name it causes this error, so i changed them by adding a 1, 2, 3 etc..
  • To post as a guest, your comment is unpublished.
    Cody · 10 months ago
    I am attempting to make this reference multiple columns and reflect the date of any change in these columns back to one column. example, if i change anything in I,J,or K I want the date updated to current in L. i have tried to copy this VB code 3 times, changing the target, and column application to be I:I and 3 J:J and 2, K:K and 1. only K:K will work to change L. thoughts?
  • To post as a guest, your comment is unpublished.
    tom Vincent · 11 months ago
    I received Run-Time Error '1004'


    This is the VBA on my Excel:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -3
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mmm-yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A:O"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123"
    End Sub


    Thank You
  • To post as a guest, your comment is unpublished.
    Vincent · 11 months ago
    I receive Run-time error '1004'

    This is the VBA on my excel:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -3
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mmm-yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A:O"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123"
    End Sub



    Thanks
  • To post as a guest, your comment is unpublished.
    Hadi Wibowo · 1 years ago
    Kalau munculnya waktu minta di bawah bagaimana?
  • To post as a guest, your comment is unpublished.
    Chris · 1 years ago
    This is exactly what I needed. Since I needed a timestamp in column F to reflect any changes in columns A, B, C, D, or E in a row, I duplicated the code that many times and redefined the range to point to each of the columns within the different iterations of the code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer

    Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
    xOffsetColumn = 5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If

    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 4
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If


    etc...
    • To post as a guest, your comment is unpublished.
      Tom · 4 months ago
      Thanks Chris, you saved me from a big headache!
    • To post as a guest, your comment is unpublished.
      MIke · 1 years ago
      Hi Chris, I see you posted the exact answer to my previous question already. I just had to expand the reading area. My bad. Thank you so much for your post. This has been so helpful.
    • To post as a guest, your comment is unpublished.
      Mike · 1 years ago
      Hi Chris, Can you let me know what part of the code I should copy and also where to paste it? I'm trying the same but am getting error codes. Preferably I would like to get a timestamp in Column F to reflect a change in Column A, and a timestamp in Column G to reflect a change in Column B. But one timestamp for a change in column A, B, C, D, or E like you did would already be great. Many thanks for your help.
  • To post as a guest, your comment is unpublished.
    Bonnie · 1 years ago
    Also, I have to say: Your instructions are THE BEST, so simple but effective and straight forward!!!

    by simply adding Rng.Offset(0, yOffsetColumn).Value = Environ$("UserName")

    above Else in the next column I am capturing the Windows Username as well, I didn't need a full Audit Trail and wanted the Change info WITH the data, this is perfect.


    Thank you so much!
  • To post as a guest, your comment is unpublished.
    Bonnie · 1 years ago
    I was wondering how would you modify the code to fill in the date only if you wanted it to populate for a given value. Say I only want the date to populate when I select "Closed" for a value for the target range?
  • To post as a guest, your comment is unpublished.
    Amr Soliman · 1 years ago
    what can i do if i need to add another range in same sheet
    when i fill in column A reflect date & time in column B and when filling in column C reflect date & time in column D
    and when i clear the content of column A or C it clear B or D too

    please help me in this
    • To post as a guest, your comment is unpublished.
      Anto. · 1 years ago
      Me too expecting for the same pl help
  • To post as a guest, your comment is unpublished.
    Amr Soliman · 1 years ago
    What if i need to another range in same sheet?
    When i fill in column A reflect date&time in column B and when i fill in column C reflect date&time in column D
    and when i clear of any column A or C it clear B & D
  • To post as a guest, your comment is unpublished.
    J Hayes · 1 years ago
    This was very helpful. Thank you! :-)
  • To post as a guest, your comment is unpublished.
    shiva · 1 years ago
    how to make this work, if there is updated in E:E range as well?
  • To post as a guest, your comment is unpublished.
    Asela · 1 years ago
    Here is the macro to update Modified date to column F on each row.

    ------------------------------------------------

    'This Macro has been written to update Last modified date/time on each A2:D43415
    'Last Modified date applied to column F.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    Dim tColInt As Integer

    tColInt = 6 'Column Index, Example: A=1, B=2, ...... ,Z=26


    Set rInt = Intersect(Target, Range("A2:D43415")) 'Change cell range
    If Not rInt Is Nothing Then
    For Each rCell In rInt
    Set tCell = Cells(rCell.Cells.Row, tColInt)
    If IsEmpty(tCell) Or Not IsEmpty(tCell) Then
    tCell = Now
    tCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM" 'Custom Format
    End If
    Next
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    Juttskt · 1 years ago
    example
    Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E,F:F,G:G,H:H"), Target)
    xOffsetColumn = 8
    i want any entry change in column E, F, G, H then date can stamp column M

    please help me in this
  • To post as a guest, your comment is unpublished.
    I wayan · 1 years ago
    Hi all, the code is working fine but i does not work when the worksheet is protected. although I have unprotected the column the certain column for the timestamp is. i am attaching the error message pop up. please help.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 1
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      ChoppaChewy · 2 months ago
      You can unlock the sheet before and after the if to make it work:


      Private Sub Worksheet_Change(ByVal Target As Range)
      'Update 20140722
      Dim WorkRng As Range
      Dim Rng As Range
      Dim xOffsetColumn As Integer
      Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
      xOffsetColumn = 1
      If Not WorkRng Is Nothing Then



      ActiveSheet.Unprotect



      Application.EnableEvents = False
      For Each Rng In WorkRng
      If Not VBA.IsEmpty(Rng.Value) Then
      Rng.Offset(0, xOffsetColumn).Value = Now
      Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm"
      Else
      Rng.Offset(0, xOffsetColumn).ClearContents
      End If
      Next
      Application.EnableEvents = True



      ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
      False, AllowFiltering:=True



      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    I Wayan Murdikayasa · 1 years ago
    Hi all,

    i use the code given and it works perfectly. but it does not working whenever the sheet is locked, although, i already unlocked the column form the time stamp is. can you help? thank you in advance.
  • To post as a guest, your comment is unpublished.
    Ali · 1 years ago
    Plz Reply


    Every thing is perfect ...every time i save the script but when i close the application and reopen it the script does not work and i need to write/copy it again.
    thanx
  • To post as a guest, your comment is unpublished.
    Ali · 1 years ago
    Every thing is perfect ...every time i save the script but when i close the application and reopen it the script does not work and i need to write/copy it again.
    thanx
  • To post as a guest, your comment is unpublished.
    Deividas · 1 years ago
    Hello,


    Is it possible to implement this code with the data refresh. Because when i refresh the data it always puts the now stamp, however when the data is changed manually it does it okay and only records when manually changed correctly so all the times are then different, but when refreshed it might create new rows and therefore it puts all the current timestamps.
  • To post as a guest, your comment is unpublished.
    Terri Blackwell · 1 years ago
    How do I get this code to put the date stamp in the column to the left? I tried -1 and it didn't work. Also, it doesn't seem to recognize data filled in by a formula. Can I do that? I need this for twelve different columns in one Summary sheet that gathers data from six departmental sheets. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    sri laqshya · 1 years ago
    Hi, Can anyone one suggest a code for when I input number in column A and Column B and in Column C I kept a formula like Column A + Column B. Now I need a vba code that can give time and date in Column D whenever Column C changes not when inserting numbers in Column A and B.
  • To post as a guest, your comment is unpublished.
    Petras · 1 years ago
    Hello,
    How to define exact column for timestamp instead offset ?
  • To post as a guest, your comment is unpublished.
    Gábor · 1 years ago
    Hi,
    the code runs perfectly, this is what I was searching for. One little update / help needed:
    I have the data in column B, the time is in column AA, and have another data in column A. It would be great if you could modify the code, so it will run only if the values in B and A are matching.

    Thanks in advance.
    • To post as a guest, your comment is unpublished.
      Gábor · 1 years ago
      I have figured out. It's already down
      • To post as a guest, your comment is unpublished.
        Deividas · 1 years ago
        Hello, could you share how you done it?
        • To post as a guest, your comment is unpublished.
          Gábor · 1 years ago
          Hello there,
          Update the original code with that.
          In the meanwhile I have put another IF statement update before the code " If Rng.Offset(0, 1).Value = 0 Then ", because whenever you delete an upper row, it will cause cell value change, an will stamp the time again.

          If Rng.Offset(0, 1).Value = 0 Then
          If Not VBA.IsEmpty(Rng.Value) And Rng.Offset(0, -1).Value = Rng.Offset(0, 0).Value Then

          Hope it helped to you. :)
  • To post as a guest, your comment is unpublished.
    Gábor · 1 years ago
    Hi,
    the code works perfectly, this is what I was searching for. Thanks.
    One more little help / update about the code. I have the data in column B, the date+time is in AA, and another data in column A. Can you please help to modify the code, so it will run only if value in B and A is the same.

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    Rodney · 1 years ago
    I am having trouble getting the code to do what I want and think I am missing a step.



    I have a spreadsheet that I would like TODAY returned into Column N whenever an "X" is placed in either Column O or P, and return to blank if that "X" is deleted. What am I missing in code that will allow me to have that happen?
  • To post as a guest, your comment is unpublished.
    Hari · 2 years ago
    Tried code. Worked perfect in normal cases. But i need it little more advanced.
    I need the time & date populated in column F; keeping the columns from A to E uneditable.
    Can you help?
  • To post as a guest, your comment is unpublished.
    Jens · 2 years ago
    What if I wanted the script to look for changes to the row and then update a specific cell? What would that look like?
  • To post as a guest, your comment is unpublished.
    jazz · 2 years ago
    Worked great! Thanks!!
  • To post as a guest, your comment is unpublished.
    Morten · 2 years ago
    Thanks a bunch for the code, it works perfectly!
  • To post as a guest, your comment is unpublished.
    ankit · 2 years ago
    this code changes all previous time stamps when i modify, say Nth cell, all (N-1) cell get their time changed
    • To post as a guest, your comment is unpublished.
      Excel Rookie · 1 years ago
      I am having the same issue. I want to record the date that a specific cell was changed, For example, if B2 is changed then I want to record the date and time that happened in C2. With this code, if I change B8 or B26 it changes the time stamp in all of the C cells if the row has a value in B. (Therefore, overwriting the date that was in C2 and putting the new date in C2, C8 and C26.)
  • To post as a guest, your comment is unpublished.
    Eric · 2 years ago
    This macro/VBA works perfectly when I change the cell directly. However when I change the cell via a form, then the macro/VBA does not run and no date is added. Do you know the solution for this?
    • To post as a guest, your comment is unpublished.
      Michal · 2 years ago
      HI,

      Did you manage to resolve this? I have the same issue and it would be big help if you could share the code.
  • To post as a guest, your comment is unpublished.
    gokul · 2 years ago
    in my worksheet, i need the date and time in first column...how to do this?what are the changes i need to do with vba...
    • To post as a guest, your comment is unpublished.
      Jens · 2 years ago
      I have the same issue. Or, wel. Look for changes to the row and update a specific cell. Any leads on a solution?
  • To post as a guest, your comment is unpublished.
    your name · 2 years ago
    Thank you for your help :-)
  • To post as a guest, your comment is unpublished.
    Keith Davis · 2 years ago
    Thank you so much for the code! Exactly what I was looking for!!
  • To post as a guest, your comment is unpublished.
    Preeti · 2 years ago
    When i am record in macro a vloocup formula so how can i do that date automatically changed to 1 day up

    Like its 21/07/2017
    then next day its changed to 22/07/2017

    Automatically
    pls share if is there any vba code.
    • To post as a guest, your comment is unpublished.
      amarj · 2 years ago
      replace this line
      Rng.Offset(0, xOffsetColumn).Value = Now

      to
      Rng.Offset(0, xOffsetColumn).Value = Now+1
  • To post as a guest, your comment is unpublished.
    Lisa · 2 years ago
    this code will show date on click it appears, is it possible to show date only when content in cell is changed, if only clicked and nothing changed, then date does not show or previously entered date stays?
  • To post as a guest, your comment is unpublished.
    Nick · 2 years ago
    I found this code elsewhere and modified it for my use. If done properly, you do not need to worry about entering a function in a cell or the file changing the dates to that day's date every time it is opened.

    - open Excel
    - press "Alt+F11"
    - Double-click on the worksheet that you want to apply the change to (listed on the left)
    - copy/paste the code below
    - adjust the Range(_:_) input to correspond to the column you will update
    - adjust the Offset(0,_) input to correspond to the column where you would like the date displayed (in the version below I am making updates to column D and I want the date displayed in column F, hence the input entry of "2" for 2 columns over from column D)
    - hit save
    - repeat steps above if there are other worksheets in your workbook that need the same code
    - you may have to change the number format of the column displaying the date to "General" and increase the column's width if it is displaying "####" after you make an updated entry

    Copy/Paste Code below:
    ____________________________________________________________


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Target.Offset(0, 2) = Date

    End Sub

    ____________________________________________________________
    Good luck...
  • To post as a guest, your comment is unpublished.
    Quincey · 2 years ago
    Hi,

    I need some VBA coding help.

    I am creating a spread sheet with cases which will be assigned to people and have case status.

    The assignee's will be in Col H and I need a date stamp in Col I when the cell in Col H get updated or changed with a name.
    Similarly I will have case status in Col J and will need a date stamp in Col K when a status get inputted or changed.

    also when there is non assignee name or status I need the date stamp in Col I and K to be blank.

    Any help would be appreciated.

    Thanks
  • To post as a guest, your comment is unpublished.
    Dinesh Dumbre · 2 years ago
    How can i configure to read the A:A column and give time stamp in B:B only when a 14 digit number is entered in it ?
  • To post as a guest, your comment is unpublished.
    mike · 2 years ago
    Hi ,

    Thank you kindly for this as it is great for WB auditability.

    Is there a way of altering this to have the date/time be logged somewhere on the WB whenever a comment is added or modified? I know this is a general question, but I am not sure if it's possible (unfortuantely for MS 2010, the .comment property does not hold a date property to identify when it was entered.

    Mike
  • To post as a guest, your comment is unpublished.
    Jason · 2 years ago
    How do I get the time stamp to record in a specific cell, instead of the cell next to the one that was changed. I am attempting to have a row of cells time stamped when a change occurs in any of those cells, I would like the time stamp to record in one specific cell...can anyone help with this? Thank you!
  • To post as a guest, your comment is unpublished.
    Josh · 2 years ago
    How do I change it so only the adjacent cell date changes?
  • To post as a guest, your comment is unpublished.
    Linda · 2 years ago
    I can change the range to be ("A:Q") but I need the date and time to go in "S" if the data in any A-Q cell is changed/removed. I'm VBA challenged. Any help is greatly appreciated!
    • To post as a guest, your comment is unpublished.
      Jason · 2 years ago
      Sorry, I just posted a question and I need the answer to this...I must have missed this comment...hopefully someone can help!
  • To post as a guest, your comment is unpublished.
    Carlie · 3 years ago
    Is there a way to time stamp based on a specific change? For example, I have a spreadsheet with a column of drop-down options including being blank, 'Ok', and 'Act'. When the task is completed, the drop down is manually used to select 'Ok'. I'd like the date stamp to be tied to when 'Ok' is selected, but not when 'Act' is selected. The 'Act' option in the drop down is used when a task is past due, but I'm really just trying to track when an item is completed.

    Is there a way to accomplish this with the above code? Thanks!