是否有任何Excel公式来记录单元格的上次更改日期? [英] is there any excel formula to record cell last change date?

问题描述

我正在寻找一种无需使用Worksheet_Change事件即可在Excel中保存单元格的上次更改日期的方法.例如，定义一个这样的公式:

I'm looking for a way to save the last change date of a cell in Excel without using the Worksheet_Change event. for example define a formula like this:

=LastChangeDate(A22) 

当单元格A22更改时，当前日期显示在目标单元格中​​

and when the cell A22 changed , current date show in target cell

推荐答案

我创建了一个用户定义的函数来执行此操作，但尚未对其进行完整的测试.使用此功能之前，请注意两点:
1-此功能显示保存文档(ctrl + s)或更改其他单元格后的结果
2-仅更改目标单元的地址，而从不更改此功能中的其他参数
实际上，我们在函数的参数中记录了目标单元格的最后一个值
按着这些次序 :
1-打开MSExcel并创建新的".xlsm"文件
2按Alt + F11打开VBA窗口
3制作新模块
4-将此代码复制到Module1:

I make a User Defined Function to do that but its is not completely tested. Note two points before using this function :
1- this function shows result after you save document (ctrl+s) or change another cells
2-only change address of target cell and never change other parameters in this function
in fact we record last value of target cell in parameter of function
follow these steps :
1-open MSExcel and make new ".xlsm" file
2-press Alt+F11 to open VBA window
3-Make New Module
4-Copy this code to Module1 :

Public Function LogDate(CellAddress As String, OldValue As String, DisplayDate As String) As String

     Dim CellValue As String
     CellValue = Range(CellAddress).Value
     If CellValue = OldValue Then
        LogDate = DisplayDate
     Else
        Dim Address As String
        Address = Replace(CellAddress, "$", "")

        DisplayDate = Now
        Evaluate "ChangeFormula(""" + Application.Caller.Address(False, False) + """ , """ + Address + """ , """ + CellValue + """ , """ + DisplayDate + """)"
        
        LogDate = DisplayDate
     End If
End Function
 
Private Sub ChangeFormula(CurrentCellAddress As String, Address As String, CellValue As String, DisplayDate As String)
  Range(CurrentCellAddress).Formula = "=LogDate(CELL(""address""," & Address & "),""" & CellValue & """,""" & DisplayDate & """)"
End Sub

4-单击单元格"A2"上的并在其中写上这个公式:

4-click on a cell "A2" and write this formula inside it :

=LogDate(CELL("address",A1),"","")

目标小区地址是"A1".除了"A1"以外，不要更改任何参数.
现在，当您更改"A1"单元格值，并保存文档(ctrl + s)或更改其他单元格"A2"，显示"A1"的日期和时间.变化.

target cell address is "A1" dont change any parameter except "A1"
now when you change "A1" cell value , and save document(ctrl+s) or change other cells , "A2" Display date and time of "A1" changes .

这篇关于是否有任何Excel公式来记录单元格的上次更改日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！