跳到主要內容

單元格更改時如何自動記錄日期和時間？

對於我們來說，手動插入靜態日期和時間或通過公式插入隨系統時間變化的動態日期很容易。 如果要在更改或輸入值時自動記錄日期和時間，此問題可能會有所不同。 但是，在本文中，您可以通過以下步驟解決此任務。

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

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

例如，我有一個值範圍，現在，當我在B列中更改或鍵入新值時，我希望在那裡將在C列中自動記錄當前日期和時間，如以下屏幕截圖所示：

文檔更新時間值更改 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

文檔更新時間值更改 1

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

筆記：

1.在以上代碼中，您可以修改“B：B”更改為您要在此腳本中更改單元格值的任何其他列： 設置WorkRng = Intersect（Application.ActiveSheet.Range（“ B：B”），目標）.

2.與此 x 偏移列 = 1 腳本，您可以將日期和時間插入並更新到更改值列旁邊的第一列，可以將數字1更改為其他數字，例如2,3,4,5…，這意味著日期將被插入第二，第三，第四或第五列，以及更改後的值列。

3.當您在更改的列中刪除一個值時，日期和時間也將被刪除。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (109)
No ratings yet. Be the first to rate!
Femke
Femke
This comment was minimized by the moderator on the site
Hi,
Is it is also possible to have a VBA code that is able to create a timestamp for cells that have a formula in it? So when the value changes (changes from empty cell to a cell with a value >0 in it) the timestamp appears?
Kind regards,
Femke
Reply
0
0
skyyang
skyyang     Femke
This comment was minimized by the moderator on the site
Hello, Femke,
To solve your problem, please apply the below VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby ExtendOffice
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Dim xDRg As Range

On Error Resume Next
Set xDRg = Target.DirectDependents

If Not xDRg Is Nothing Then

Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), xDRg)

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
Else
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 If
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
Ahtisham Haider
Ahtisham Haider
This comment was minimized by the moderator on the site
Sir ! I have a problem that I have data entering Column B in sheet 1 and Date Column C in sheet 2....please guide me what will be the code for that...Anyone
Reply
Report
0
0
Fiona
Fiona
This comment was minimized by the moderator on the site
I want to use this code to update multiple cells within the same sheet. How do I write this?For example, when cell C12 changes, C13 gets the datewhen cell C26 changes, C27 gets the date
when cell G13 changes, G14 gets the date
and so on
Reply
0
0
justme
justme
This comment was minimized by the moderator on the site
datetime    auto_cycle    tool    time_difference
01-01-2021 :10:10:09    1    1    00:00:11
03-01-2021 :10:10:20    1    2    00:00:02
13-10-2021 :10:10:22    1    3    00:00:04
13-10-2021 :10:10:26    1    4    00:00:04
13-10-2021 :10:10:30    1    5    00:00:06
13-10-2021 :10:10:36    1    6    00:00:02
13-10-2021 :10:10:38    1    7    00:00:05
13-10-2021 :10:10:43    1    8    00:00:00
13-10-2021 :10:10:43    1    9    00:00:06
13-10-2021 :10:10:49    1    10    00:00:03
13-10-2021 :10:10:52    1    11    00:00:08
13-10-2021 :10:11:00    1    13    00:00:10
13-10-2021 :10:11:10    1    12    00:00:04
13-10-2021 :10:11:14    1    14    00:00:05
13-10-2021 :10:11:19    1    16    00:00:04
13-10-2021 :10:11:23    1    17    00:00:04
13-10-2021 :10:11:27    1    18    00:00:02
13-10-2021 :10:11:29    1    19    00:00:04
13-10-2021 :10:11:33    1    20    00:00:05
13-10-2021 :10:11:38    1    21    00:00:07
13-10-2021 :10:11:45    1    12    

this is my master file suppose i change the time value in a column corresponding time difference sshould be update in d column automatically without trigger the macro assigning button could you please help me out 
Reply
0
0
JustMe
JustMe
This comment was minimized by the moderator on the site
I see a couple of people asking about formulas and I'm sorry if I missed the answer somewhere in the thread. I am updating very large data sets that then are migrated to hidden sheets. I have those sheets each set with this "latest update" code but since everything on those sheets references back to the larger data set, it does not see a cell update as the formulas remain the same even thought the values change. I need it to update when the value changes and not jus when the content of the cell changes.
Reply
1
0
Ahtisham Haider
Ahtisham Haider     JustMe
This comment was minimized by the moderator on the site
Sir ! Did you find solution of your problem then please share it with me.I am also facing the same issue...I am new in this...Please
Reply
Report
0
0
Shiva
Shiva
This comment was minimized by the moderator on the site
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
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

Reply
1
0
Shiva
Shiva
This comment was minimized by the moderator on the site
1 26-02-2021, 11:32:49
1 26-02-2021, 11:32:49
1 26-02-2021, 11:32:49
1 26-02-2021, 11:32:49
1 26-02-2021, 11:32:50
1 26-02-2021, 11:32:50
1 26-02-2021, 11:32:50
1 26-02-2021, 11:32:50
1 26-02-2021, 11:32:50
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04
0 26-02-2021, 11:33:04

Why on 0 value its getting date.. when cell have data then only get date otherwise show balnk...how we can do it vba code
Reply
0
0
MOAMB
MOAMB
This comment was minimized by the moderator on the site
Hi,The code works perfect but if changed the data in A i was wondering if i could have the date in column B and the time in Column D?Thanks for any help.
Reply
Report
0
0
Narasimha
Narasimha     MOAMB
This comment was minimized by the moderator on the site
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("J:J"), 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
xOffsetColumn = 2
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
End Sub
Reply
0
0
CV
CV
This comment was minimized by the moderator on the site
Hi,If i were to have 2 columns duplicating the same macros, how can that be possible.
I want to show the date and time in column B for values changed in A; while also wanting to show the time and date in column D for values changed in C.
Reply
0
0
Phoebe
Phoebe
This comment was minimized by the moderator on the site
Hi Extend Office, thank you for guiding me.I am a beginner , i had this problem which i saw some of your faced. This was the situationFor e.g
You want your date and time shown in Column A when any other column of the same row B:AZ for example got their values changed.So heres my solution. Please correct me if the code has any issues. TIA.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:AZ"), Target)'B:AZ put your own columns'rownumber=Activecell.Row will not work because it it will locate the adjacent row after you press enter or when ur mouse click on other cells
rownumber = Target.Row
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Range("A" & rownumber).Value = Now 'gives A and the adjacent row number
Range("A" & rownumber).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
End If
Next
Application.EnableEvents = True
End If
End Sub



Thank you.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations